SQL Query in Excel VBA to pull info from Access

Question

Revised question: I'm not used to writing SQL queries in VBA so I've been using the macro recording for connecting to Access. I've included the code that the macro recorder returns. I'm getting an error message

Unexpected Error. Something went wrong. If the problem continues, please restart Excel.

If I click close on the error message then the spreadsheet with the database info from Access shows up in Excel, which is great, but I'd rather not have the error message pop up.

Here's my code so far:

Sub Contact_Search()  

Dim ContactNum As String  
Restart:  
ContactNum = InputBox("Enter the number to query.", "Contact Query", "Enter the number here...")  
If ContactNum = "Enter the number here..." Then  
  MsgBox "Invalid response, please enter the number to query."  
  GoTo Restart  
ElseIf ContactNum = "" Then  
  MsgBox "Number is mandatory.  Please enter number."  
  GoTo Restart  
End If  

ActiveWorkbook.Worksheets.Add After:=Sheets(1)  
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _  
  "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\path info\folder name\Contacts " _  
  , _
  "Database.accbd;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Da" _  
  , _
  "tabase Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mod=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Globa" _  
  , _  
  "l Bulk Transactions=1;JetOLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False" _  
  , _  
  ";Jet OLEDB:Don't Copy Local on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Suppo" _  
  , _  
  "rt Complex Data+False;Jet OLEDB:Bypass User Info Validaton=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB;Bypass ChoiceField" _  
  ,  " Validation=False"), Destination:=Range("$A$1")).QueryTable  
.CommandType = xlCmdTable
.CommandText = Array("Contacts")
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells  
.SaveData = True  
.AdjustColumnWidth = True
.RefreshPeriod = 0  
.PreserveColumnInfo = True  
.SourceDataFile = "C:\Users\path info\folder name\Contacts\Database.accdb"  
.ListObject.DisplayName = "Table_Database.accdb"  
.Refresh BackgroundQuery:=False  
End With

End Sub

I'm sure that much of what's in this code is not really needed, it's just stuff that the macro recorder puts in there but I'm not sure what is ok to take out and what has to be in there for it to work and I'm not sure if something in the code is causing the error message that I'm getting. As I said, the info is still coming over, but I have to close out of the error message before it shows up in the excel doc.

Also, what I'm actually wanting to return is not the entire table, but only the rows that match the variable ContactNum that is input by the user. I'm not sure where in this code I would put the SQL language to only return the specific values rather than the entire table. Any ideas on the error message as well as the SQL verbiage?

Any particular reason you can't use an ODBC connection to your Access instance, your queries can be generated and stored in Access. You can then reference these directly from within EXCEL as a linked table. - Here is a good place to start - https://support.office.com/en-us/article/connect-to-an-access-database-a3d6500c-4bec-40ce-8cdf-fb4edb723525. You can then simply reference your Access query using VBA — jimmy8ball, Feb 06 '19 at 11:45
Agree that creating queries in Access and then connecting through Excel by creating a data source/connection there is the way to go. I would definitely avoid creating queries in VBA. The only reason I can think of for that is if you're trying to develop some interactive Excel application that requires generating queries on the fly. But also, this is too general a question for this forum. The idea here is that you have a specific problem for which you haven't been able to find a solution. Also, you're asking for "recommendations" which generates opinions--something we try to avoid. — Jamesckel, Feb 06 '19 at 11:59
You don't actually need a query to update **Access** database from excel. You could just open your table and filter it.. [have a look at Gord's answer](https://stackoverflow.com/questions/15709156/vba-code-to-update-create-new-record-from-excel-to-access) — Zac, Feb 06 '19 at 13:23

score 0 · Accepted Answer · answered Feb 06 '19 at 14:32

I use DAO rather than ADO for my connections to Access databases. Below is an example of a typical Sub that I use.

Sub AccessSQL(ByVal Var1 As String, ByVal Var2 As String)
    Dim DBPath As String
    Dim i As long
    Dim j As long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim xlCell As Range
        Set xlCell = Range("A1")
        DBPath = "C:\AccessDBS\DataDB.accdb"
        sSQL = "SELECT TableA.Field01, TableA.Field02, TableA.Field03 FROM TableA WHERE (((TableA.Field01) = '" & Var1 & "') And ((TableA.Field02) = '" & Var2 & "') And ((TableA.Field03) = 0) And (Not (TableA.Field04) = 0)) Or (((TableA.Field04) = 99999)) ORDER BY TableA.Field01;"
        Set db = OpenDatabase(DBPath)
        Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
        If Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
            i = rs.RecordCount
            If i > 0 Then
                rs.MoveFirst
                For j = 1 To i
                    With xlCell
                        .Value = rs!Field01 & " - " & rs!Field02 
                        .Offset(0,1).Value = rs!Field03
                    End With
                    rs.MoveNext
                    Set xlCell = xlCell.Offset(1,0)
                Next j
            Else
                xlCell.Value = "No Records Returned"
            End If
        Else
            xlCell.Value = "No Records Returned"
        End If
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
        Set xlCell = Nothing 
End Sub

The above sub is being fed 2 string variables which are incorporated into my SQL SELECT statement. It then cycles through the records returned and places them into the excel sheet. Of course depending on what you need to do with the data that is returned, you can expand this to perform calculations or to populate combo boxes on forms or whatever.

When I try to run this I get a Compile Error: User-defined type not defined for DIM db As DAO.Database. Is there some setting I need to turn on or something that needs downloaded for the DAO to work? — Bonnie Z, Feb 06 '19 at 14:45
@BonnieZ - Yes, you need to make sure that your project has a reference to the Microsoft Data Access Objects. In the VBA editor, under the tools menu, select "References" then make sure you have a check next to the item listed as "Microsoft Access xx.x Object Library" Mine is listed as "Microsoft Access 15.0 Object Library" I also have "Microsoft Office 16.0 Access database engine Object Library" checked — Glenn G, Feb 06 '19 at 14:58
Great! Got to bulk of the code to run, but am now getting an error regarding my SQL query when it tries to run the code, .Open stSQL1, cnt. I'm thinking it has something to do with the variable I'm trying to use for my SQL query. Here's the query. stSQL1 = "SELECT * FROM Contacts WHERE ((Contact_Num) = '" & ContactNum & "');" ContactNum has been previously assigned a value from a MsgBox input request. Any idea what is wrong that's causing an error? — Bonnie Z, Feb 07 '19 at 07:09
@BonnieZ - To pass a string variable to into your select statement you need to enclose it in quotes. To to this through VBA, you will need to use single quotes so that the compiler knows the difference. Try stSQL1 = "SELECT * FROM Contacts WHERE ((Contact_Num)='" & ContactNum & "');" note the single quotes inside my double quotes — Glenn G, Feb 07 '19 at 12:53

SQL Query in Excel VBA to pull info from Access

1 Answers1