Run an SQL Query for Access from Excel

Question

Hi friends,

Suppose, I have some data in an Access Database, that is placed at:

E:\eLaboratory\DBs\PDs.accdb

Can anyone present me with a simple example of an SQL command like:

Select * from TableA

but in VBA code

so as to return to the activecell the result of the same, instead of common manner of writing such a query in the database itself and thereafter be exported to Excel or imported from Excel.

Main object is to make an Excel macro carryout the said task.

Thanx in advance.

Answer 1

Re read your post, this is what you really want

Public cn                               As New ADODB.Connection

Public rs                               As New ADODB.Recordset

Sub CopyTableFromAccess()

Dim a As Integer

With cn

.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\database.accdb"

.Provider = "Microsoft.ACE.OLEDB.12.0"

.Open

End With

rs.Open "Table1", cn

a = 2

With rs

Do While Not .EOF

Cells(a, 1) = .Fields("Clemson")

Cells(a, 2) = .Fields("Tigers")

a = a + 1

.MoveNext

Loop

End With

rs.Close

cn.Close

End Sub

Just figured it out for you!!!!!!

PS, add the References like above

Answer 2

Hi Rich,

Similar problems occured with me while using Joins and Pivot complex commands in a single go.

Try to carry out the process with complex queries broken to simpler statements and then using the consolidation filteration summarization in the end.

Best of luck.

Answer 3

I'm trying to run this for a work query and it works, unless I have more than 32,000 being run. I receive an overflow error. Any suggestions?

Public cn As New ADODB.Connection

Public rs As New ADODB.Recordset

Function RawData()

'Disable the screen.

    Application.ScreenUpdating = False

'this conects the excell document to the access document

With cn

    .ConnectionString = "C:\Users\rich.wolff\Desktop\2014POSDatabase\HMKPOSDatabase2014.accdb"

    .Provider = "Microsoft.ACE.OLEDB.12.0"

    .Open

End With

'then to run a query  I show one i have done so you can see how it works

Dim SQL As String

Dim i As Integer

 SQL = "TRANSFORM Sum(StoreSalesData.qty) AS SumOfqty"

 SQL = SQL & " SELECT CensusDataBrief.IncomePerHousehold, CensusDataBrief.AverageHouseValue, StoreSalesData.DEL_DOC_NUM"

 SQL = SQL & " FROM CensusDataBrief INNER JOIN (VSNConversionData INNER JOIN (([Divisional Vice President List] INNER JOIN ([District Sales Manager List] INNER JOIN (([Tempur-Pedic Reps] INNER JOIN [Sleepys Store List] ON [Tempur-Pedic Reps].[Territory Name] = [Sleepys Store List].TPXTerritory) INNER JOIN StoreSalesData ON [Sleepys Store List].[Store Code] = StoreSalesData.STR) ON [District Sales Manager List].DSM = [Sleepys Store List].DSM) ON ([Divisional Vice President List].DVP = [Sleepys Store List].DVP) AND ([Divisional Vice President List].DVP = [District Sales Manager List].DVP)) INNER JOIN [Market Sales Director List] ON ([Market Sales Director List].MSD = [District Sales Manager List].MSD) AND ([Market Sales Director List].MSD = [Sleepys Store List].MSD) AND ([Divisional Vice President List].DVP = [Market Sales Director List].DVP)) ON VSNConversionData.VSN = StoreSalesData.VSN) ON CensusDataBrief.ZipCode = [Sleepys Store List].Zip"

 SQL = SQL & " WHERE (((CensusDataBrief.IncomePerHousehold)=""$50,000-$99,999"") AND ((StoreSalesData.[WRTN DT]) Between #4/1/2014# And #6/30/2014#) AND ((VSNConversionData.[Item Type])=""mattress"") AND ((VSNConversionData.[Item Brand])=""Tempur-Pedic"")) OR (((CensusDataBrief.IncomePerHousehold)=""$50,000-$99,999"") AND ((StoreSalesData.[WRTN DT]) Between #4/1/2014# And #6/30/2014#) AND ((VSNConversionData.[Item Type])=""foundation"" Or (VSNConversionData.[Item Type])=""bedbase - adjustable"" Or (VSNConversionData.[Item Type])=""Pillows""))"

 SQL = SQL & " GROUP BY CensusDataBrief.IncomePerHousehold, CensusDataBrief.AverageHouseValue, StoreSalesData.DEL_DOC_NUM"

 SQL = SQL & " PIVOT VSNConversionData.[Item Type];"

i = 1

With rs

    .Open SQL, cn 'inputs SQL into access

    Do While Not .EOF 'runs loop until no more data avalable

        Cells(i, 1) = .Fields("IncomePerHousehold")

        Cells(i, 2) = .Fields("AverageHouseValue")

        Cells(i, 3) = .Fields("Del_Doc_Num")

        Cells(i, 4) = .Fields("Mattress")

        Cells(i, 5) = .Fields("Foundation")

        Cells(i, 6) = .Fields("Bedbase - Adjustable")

        Cells(i, 7) = .Fields("Pillows")

        i = i + 1

        .MoveNext 'moves to next line in access

    Loop

End With

rs.Close 'closes SQL

' after you have run all the queries you need

cn.Close

   'Enable the screen.

    Application.ScreenUpdating = True

End Function

Answer 4

Tremendous solution Eric!

Thanx buddy.

Answer 5

I got you man,

fist you must add a reference, go to tool, References, and then scroll down to "Microsoft ActiveX Data Objects 2.8 Library" and put a check next to it

'at the top of the Modual not in any sub

Public cn                       As New ADODB.Connection

Public rs                       As New ADODB.Recordset

'Then in the sub use

'this conects the excell document to the access document

With cn

.ConnectionString = "E:\eLaboratory\DBs\PDs.accdb"

.Provider = "Microsoft.ACE.OLEDB.12.0"

.Open

End With

'then to run a query  I show one i have done so you can see how it works

Dim SQL as string

Dim i as integer

SQL = "SELECT PullFileLocation.[Push Trending Variable], PullFileLocation.[Number Of" & _

" Samples] FROM PullFileLocation GROUP BY PullFileLocation.[Push Trending Variab" & _

"le], PullFileLocation.[Number Of Samples];"

i = 1

With rs

.Open SQL, cn 'inputs SQL into access

Do While Not .EOF 'runs loop until no more data avalable

cells(i, 1) = .Fields("Push Trending Variable") 'fills trend type array to the push variables

cells(i, 2) = .Fields("Number Of Samples") 'fills trend type array with number of samples

i = i + 1

.MoveNext 'moves to next line in access

Loop

End With

rs.Close 'closes SQL

' after you have run all the queries you need

cn.close

This code will move throw each row in the qurry

Tell me if this is not clear enough and I will clarify