First attempt at ADO a dismal failure

[djsmiffy]

Hi hope someone can help

I'm trying to use ADO in access to lookup product info from a few tables. The table to use depends on the choices the user selects in three combo boxes. I just can't get it to connect to any of the tables with my code:

Private Sub PopulateGrade()
    Dim cnn1 As ADODB.Connection
    Dim GdRs As New ADODB.Connection
    Dim SQLst As String
 
    Set cnn1 = CurrentProject.Connection
 
    SQLst = "SELECT Product, Flute FROM "
    SQLst = SQLst & ComboMill.Value & ComboBoard.Value
    SQLst = SQLst & " WHERE (((Flute)='"
    SQLst = SQLst & ComboFlute.Value & "'"
 
 
    GdRs.Open SQLst
End sub
 

ComboMill, ComboBoard and ComboFlute's row source type are set to value list and contain the suppliers names, general material type and material thickness. Combining ComboMill and ComboBoard gives the table name (or should).

When I run the code I get:

Run-Time Error '-2147467259(80004005)':

[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified.

I need to know if it's looking for the data source in the right place as the name should be correct and what's a default driver and how do you specify one.

Any help you can offer is most welcome as this is driving me nutts

Many thanks
Dave

[Rabbit]

Please use code tags.

Have the code either MsgBox or Debug.Print the SQL string before trying to execute it. See if it's at least returning what it should be.

Your SQL string has too many unclosed parentheses.

[FishVal]

Hi, Dave.

To open connection you need to use connection string, not SQL statement which is used to open recordset or run command on opened connection.
To see what is JetOLEDB connection string looks like go to VBA immediate window and run:

? CurrentProject.Connection

many of the string arguments are in most cases optional, but the following two you need to provide
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\.....\db1.mdb"

BTW, if you want to access datasets in current database you should use that opened and ready to use connection.

Regards,
Fish