General ODBC error

[ssjody]

In Excel 2002 using VBA, I am attempting to do a Database query. I have a simple form that contains a textbox and a command button to run the code. I recorded a macro which grabbed most of the code I needed to run the query. I then copied that code to my form command button. I am setting a variable but the code keeps breaking on the .Refresh BackgroundQuery:=False attribute. When the code gets to this point I get a 'Run-Time error '1004': General ODBC Error. When I press the Debug button it stops on .Refresh BackgroundQuery:=False piece of code.

Can someone point me in the right direction and tell me where I am going wrong? Any advice at all will be appreciated. Thanks! Jody

Private Sub CommandButton1_Click()
Dim tb As String
tb = TextBox1.Value

With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=CHECKMATE;SERVER=127.0.0.1;PORT=1972;DATABASE=ATI;AUTHENTICATION METHOD=0;UID=_system;PWD=SYS;QUERY TIMEOUT=1" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT Inventory.Part, Inventory.Interchange, Inventory.Side, Inventory.Description, Inventory.Location" & Chr(13) & "" & Chr(10) & "FROM SQLUser.Inventory Inventory" & Chr(13) & "" & Chr(10) & "WHERE (Inventory.Location=" & tb & ")")
        .Name = "Query from CHECKMATE"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False '<< This is where my Code breaks and wont go any further
    End With
End Sub

[Leith Ross]

Hello ssjod,

I am not clear on what you are wanting to do here. The macro you recorded adds a Query Table to the worksheet. Adding this code to a command button will result in an identical Query being added to the same worksheet. I am guessing you want to have is a Query Table, that once created, will be refreshed at regular intervals from the same database. Let me know.

Thanks,
Leith Ross

[ssjody]

Hi Leith,

Yes. I looking to be able to query the database. I want the ability to set one of the values in a textbox on the form and then pass that to the query.

For example, I want to Select all parts that have a location that I can type into the textbox on my form. When I press the command button, I'll get back the results in my spread sheet for all parts in that location. That way I don't have to edit the query in Excel everytime I run the query, just to change the location. I can just change it from the form and run the query.

Thanks Jody

[ssjody]

Bump Any ideas, anyone?