How do i change a query in Excel to Access

[chaostheory]

I have the worksheet setup through the data import to pull a query from a database and it works great. The problem is each time i run the sheet i have to go into access and change the number of the query i want to pull before i do the import. I have a feeling its with a sql string but im not sure i've never done it before.

How can i pass that number from excel into the query so it only returns the test results for the project number i want instead of going into access to modify the query each time just to change one number??

Right now all that shows in VBA is

Selection.QueryTable.Refresh BackgroundQuery:=False

I need to be able to modify the WHERE part of this query. The data im pulling is based on an test number. Each time i get a notification a test is done i go into the database and change the query in design view and just put the test number in the "Criteria" so that only that tests results are returned. That criteria is what i want to pass from excel to eliminate the going into access at all.

Thanks for any help, it is appreciated.

 

[chaostheory]

Ok i managed to get a SQL String to query my database, but im stuck... Something about my syntax and trying to use a variable in the sql string is messing it up. I setup my spreadsheet. I put the STR number in cell M2 and hit go.

Im getting "General ODBC Error"

Sub proSQLQuery1()
Dim varConn As String
Dim varSQL As String
Dim strNum As String
     Range("A1").CurrentRegion.ClearContents
     strNum = Range("M2")
     varConn = "ODBC;DBQ=Z:\_AC_LBP\Database\Forms and Macros for DB\2003_Lims for DB.mdb;Driver={Driver do Microsoft Access (*.mdb)}"
     varSQL = "SELECT * FROM LIMS_BLIMS_STR_RESULTS WHERE STRNO = " & strNum & ""
        With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
            .CommandText = varSQL
            .Name = "Query-39008"
            .Refresh BackgroundQuery:=False
        End With
End Sub

 

[chaostheory]

I got it, sorry for wasting your time, you can delete this post if you like.

I had to add a ' ' around the str number i was retrieving from the cell to make it sit in the sql correctly.

Sub proSQLQuery1()
Dim varConn As String
Dim varSQL As String
Dim strNum As String
     Range("A1").CurrentRegion.ClearContents
     strNum = Range("M2")
[COLOR=red]     strNum = "'" & strNum & "'"  ' added this[/COLOR]
     varConn = "ODBC;DBQ=Z:\_AC_LBP\Database\Forms and Macros for DB\2003_Lims for DB.mdb;Driver={Driver do Microsoft Access (*.mdb)}"
     varSQL = "SELECT * FROM LIMS_BLIMS_STR_RESULTS WHERE STRNO = " & strNum & ""
        With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
            .CommandText = varSQL
            .Name = "Query-39008"
            .Refresh BackgroundQuery:=False
        End With
 
End Sub