Return results of SQL Server query to Access (1 Viewer)

[gwunta]

Hi all

I have an Access 2010 application that talks to SQL Server. Part of the application creates tables on the SQl Server and inserts data to it (I know SQL Server has its on data inport/export wizard and bulk insert capabilities but for reasons outside of my control it needs to be done from Access). So I have a connection to the SQl Server and i have VBA that happily executs SQL statements on the SQl Server. The problem is I want to return a recordset from the result of a SQL query issued to the SQL Server. The idea being to check for the presence of tables already created, and if so, append to the already existing table, otherwise if it does not exist, create it and insert data to it.

Ive tried the following code but it seems no recordset is being returned:

Set objConnection = New ADODB.Connection
objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes;DATABASE=" & Me.Combo54
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'"
strSQL = "USE " & Me.Combo54 & " " & strSQL
Set Rst = cmd.Execute("USE " & Me.Combo54 & " " & strSQL)

Thanks in advance

 

[ino_mart]

Try this

dim objConnection as ADODB.Connection
dim rs as ADODB.Recordset
 
Set objConnection = New ADODB.Connection
set rs = New ADODB.Recordset
objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes; DATABASE=" & Me.Combo54
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'"
 
With rs
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open strSQL, objConnection
End With

If you have a form with textboxes where field "control source" matches with a fieldname in your SQL-table, the actual values will be filled in if you add next line under "End With"

Set Me.RecordSource=rs