Dynamic Query SQL with VB 
Author Message
 Dynamic Query SQL with VB

I would like to dynamically query my SQL database...

The catch is though..my user will type the name of the
table they would like to query...
This statement generates an error.. anyone know why?
Table  will always contain the name of a table... string

With RS
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .open "Select * FROM '" & Table & "'", cn
End With

Thanks,,,
P



Sun, 01 May 2005 05:12:54 GMT  
 Dynamic Query SQL with VB

Quote:

> I would like to dynamically query my SQL database...

> The catch is though..my user will type the name of the
> table they would like to query...
> This statement generates an error.. anyone know why?
> Table  will always contain the name of a table... string

> With RS
>     .CursorLocation = adUseClient
>     .LockType = adLockBatchOptimistic
>     .open "Select * FROM '" & Table & "'", cn
> End With

> Thanks,,,
> P

I thought you shouldn't enclose table names in ' ? So it should be:

Select * FROM Customers

or:

Select * FROM [Customers]

--
Max Bolingbroke MCP
http://www.bolingbroke.net/novanet/



Sun, 01 May 2005 05:31:01 GMT  
 Dynamic Query SQL with VB

Quote:
> I would like to dynamically query my SQL database...

> The catch is though..my user will type the name of the
> table they would like to query...
> This statement generates an error.. anyone know why?
> Table  will always contain the name of a table... string

> With RS
>     .CursorLocation = adUseClient
>     .LockType = adLockBatchOptimistic
>     .open "Select * FROM '" & Table & "'", cn
> End With

> Thanks,,,
> P

Try...

Dim rs As New ADODB.Recordset
Dim strQuery as String
Dim strTable as String

strTable = "myTable"
strQuery = "SELECT * FROM '" & strTable & "'"

rs.Open strQuery, cn, adOpenDynamic, adLockBatchOptimistic

HTH
Brad



Sun, 01 May 2005 07:36:18 GMT  
 Dynamic Query SQL with VB


Quote:
> I would like to dynamically query my SQL database...

> The catch is though..my user will type the name of the
> table they would like to query...
> This statement generates an error.. anyone know why?
> Table  will always contain the name of a table... string

> With RS
>     .CursorLocation = adUseClient
>     .LockType = adLockBatchOptimistic
>     .open "Select * FROM '" & Table & "'", cn
> End With

Get rid of the pair of single quotes.  Strings are enclosed in quotes (such
as a string that is part of a WHERE clause), table names are not.

Mike



Sun, 01 May 2005 22:22:28 GMT  
 Dynamic Query SQL with VB
Tables are not enclosed in quotes.

Dim rs As New ADODB.Recordset
Dim strQuery as String
Dim strTable as String

strTable = "myTable"
strQuery = "SELECT * FROM " & Trim(strTable)
rs.Open strQuery, cn, adOpenDynamic, adLockBatchOptimistic
rs.Open strQuery, cn, adOpenDynamic, adLockBatchOptimistic

Alternatively,

Dim oCnn as new ADODB.Connection
Dim oRS as ADODB.Recordset
oCnn.ConnectionString = "Your connection string"
oCnn.Open

Set oRS  = oCnn.Execute "Select * from " & Trim(strTable)

This will work if u dont want a dynamic or a static
recordset.

Hope this helps.

- Seshadri.

Quote:
>-----Original Message-----
>> I would like to dynamically query my SQL database...

>> The catch is though..my user will type the name of the
>> table they would like to query...
>> This statement generates an error.. anyone know why?
>> Table  will always contain the name of a table... string

>> With RS
>>     .CursorLocation = adUseClient
>>     .LockType = adLockBatchOptimistic
>>     .open "Select * FROM '" & Table & "'", cn
>> End With

>> Thanks,,,
>> P

>Try...

>Dim rs As New ADODB.Recordset
>Dim strQuery as String
>Dim strTable as String

>strTable = "myTable"
>strQuery = "SELECT * FROM '" & strTable & "'"

>rs.Open strQuery, cn, adOpenDynamic, adLockBatchOptimistic

>HTH
>Brad

>.



Wed, 04 May 2005 02:50:13 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Dynamic SQL Queries, dynamic reports

2. Create a Dynamic generated SQL query in VB/VBA

3. Converting dynamic query by example SQL to RDO based SQL Stored Procedure

4. SQL query: from SELECT statement to stored procedure with dynamic PL/SQL

5. how can I build 2 queries in 1 query(VB SQL query)

6. dynamic pass-through query to MS-SQL server

7. Stored queries vs. Dynamic SQL directly in code

8. DYNAMIC SQL QUERY

9. Dynamic SQL query

10. Dynamic SQL Queries

11. STORED QUERY Vs DYNAMIC SQL WARNING?????

12. Decimal point in Regional Settings affects dynamic SQL query

 

 
Powered by phpBB® Forum Software