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 access 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 supplier name, 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  

Thanks Dave

Hi
Have you checked the sql statement is correct? No spaces inthe middle of the tables names, etc.?


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Hello. In addition to stickleproject's tips, you got to ensure the connection is open, before attempting to retrieve records from the db. And there's no need to declare a local ADODB.Connection variable: you can simply specify the global connection to the recordset, and it will use it. But you GOT to specify the connection for the recordset to use to be open:

' assumes that the CurrentProject.Connection object is a valid and currently open connection     GdRs.Open SQLst, cnn1     ' (If the connection is open, this also works, and it doesn't need any additional variable declaration)     ' GdRs.Open SQLst, CurrentProject.Connection

And more, you better should specify the cursor type:

GdRs.Open SQLst, cnn1, adOpenForwardOnly

... and the lock type...

GdRs.Open SQLst, cnn1, adOpenForwardOnly, adLockReadOnly

... and the type of request too, in order to speed up ADO's retrieval work

GdRs.Open SQLst, cnn1, adOpenForwardOnly, adLockReadOnly, adCmdText

Well, in fact you better deepen ADO's usage. Maybe you would appreciate looking this sample project I posted time ago.

Hope it helps.

____________________________
Real Programmer can count up to 1024 on his fingers