Import data from Access to Excel

[taporctv]

Hello, Im new to the forums. I love this forum already. After a couple minutes of searching I finally found out how to make a connection string to my Access db.

My question is about querying Access. I have a query already stored in access that I would like to execute from excel and return the result to a specified cell. Is this possible or is there another method I should consider?

[mdmackillop]

Have a look at this KB item

Change the Table source to the Query source eg
[VBA]
'Create query
'sSQL = "SELECT Table1.Data, Table1.Count FROM Table1;"
sSQL = "SELECT Query1.Data, Query1.Count FROM Query1;"
[/VBA]Where Table1 and Query1 are the names of the Access objects.

[taporctv]

Here's my code that works.
[VBA]Private Sub Query1CheckBox_Click()

If Query1CheckBox.Value = True Then

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\IntTesting\IA Testing.mdb;"
Set Rs = CreateObject("ADODB.recordset")
Dim sql As String
sql = "SELECT COUNT (*) FROM " _
& "(SELECT DISTINCT [SV-5].NAME1 " _
& " FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAME1 = SFtoFD.NAME2) LEFT JOIN SFtoSV4 ON [SV-5].NAME1= SFtoSV4.NAME2 " _
& " WHERE ([SV-5].Name1 AND SFtoFD.NAME2) IS NULL AND ([SV-5].Name1 AND SFtoSV4.NAME2) IS NULL);"
Rs.Open sql, conn

Sheets("Sheet2").Range("A1").CopyFromRecordset Rs
End If

If Query1CheckBox.Value = False Then
Sheets("Sheet2").Range("A1").Delete
End If

End Sub[/VBA]

Now, is it possible to put the connection string in a function? Im going to have around 20+ check boxes and I dont want to recreate the connection string within every procedure.

[mdmackillop]

Something like this (untested), assuming you have a different SQL for each button.
[VBA]Option Explicit
Dim RS

Private Sub Query1CheckBox_Click()
If Query1CheckBox.Value = True Then
Dim sql As String
sql = "SELECT COUNT (*) FROM " _
& "(SELECT DISTINCT [SV-5].NAME1 " _
& " FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAME1 = SFtoFD.NAME2) LEFT JOIN SFtoSV4 ON [SV-5].NAME1= SFtoSV4.NAME2 " _
& " WHERE ([SV-5].Name1 AND SFtoFD.NAME2) IS NULL AND ([SV-5].Name1 AND SFtoSV4.NAME2) IS NULL);"
End If
If Query1CheckBox.Value = False Then
Sheets("Sheet2").Range("A1").Delete
End If
End Sub

Sub RunSQL(sql As String)
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\IntTesting\IA Testing.mdb;"
Set RS = CreateObject("ADODB.recordset")
RS.Open sql, conn
Sheets("Sheet2").Range("A1").CopyFromRecordset RS
End Sub
[/VBA]

[taporctv]

Thanks that worked. Is it safe to have RS as a global variable?

[mdmackillop]

The Public variable is left over from a change of mind. It doesn't serve any purpose in the code and can be dimmed within Runsql

[AleemAM123]

Hi mdmackillop,

I realise this is an old thread but I am trying to use excel vba to get some tables and query results from an access file. This is my first foray into this sort of thing so I downloaded the files from the KB article (http://vbaexpress.com/kb/getarticle.php?kb_id=889) to see how it works. I am getting a runtime error 3706 at this line in the code:

.Open MyConn

I am using windows 7 and office 2010, I checked that the jet dlls were in the C:\Windows\Syswow64 folder. What else do I need to look at to get this to work?