ODBCconnection -PLEASE HELP

[DPUser]

I have a database that has to connect to another database through an ODBC. So I decided to connect with the method that I know how to do and after the main part of database is done, I could then handle the connection through VBA. The temporary method I am using is:
-right click on tables
-select link tables
-click on files of type
-scroll down to ODBC databases
-go to "Machine Data Source" tab
-select desired database
-enter pass/user
-select desired tables
A world icon appears

Now that the main part of the database is done. I have to be able to connect through VBA and connection string. I have found an example of connecting to an ODBC in a book. It is as follows:


Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset

cnn1.Open "Provider=SQLOLEDB.1;User ID=ictspass;pwd=ictspass;SERVER=BLADE1\BLADE1_SQLSVR1"
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic '
rst1.Open "exch_tools_trade", cnn1, , , adCmdTable
Do Until rst1.EOF
Debug.Print rst1.Fields(12).Value
rst1.MoveNext
Loop

This works fine. However, now when I do my SQL select statements, It says that it cannot find the table. Is there something that I am doing wrong?

Thank you,

 

[cmmrfrds]

rst1.Open "exch_tools_trade", cnn1, , , adCmdTable

The 5th parameter adCmdTable is telling ADO that you are using a table name. If you leave the parameter off it defaults to Text or replace with the text parm. Also, if you want to update the recordset or see the record count, etc.., then make the 3rd and 4th parameters 3.

rst1.Open "exch_tools_trade", cnn1, 3, 3

The adLockOptimistic is same a the 3, but a client side static cursor is easiest to work with - rst1.CursorType = adOpenStatic