Query from Excel to Access - Passing Parameters?

[Marc T]

Hi all,

I have an Access Database which combines data from an external source
(Oracle based) with it's own data. Within the database is a query which
prompts for input such as 'choose week'. I need to run this query and
manipulate the data in Excel.... is there any way to pass the 'week'
parameter from Excel as can be done when doing a simple query via MS query? I
have tried a few ways but keep getting a Parameter Expected error.

I suppose the problem is trying to pass the parameter from Excel to MS
Query, and then on to Access.

Any advice most appreciated!
Marc

 

[Joel]

If you are using MS Query then you can take the SQL statement from the MS
Query and put into the Excel query. When you use the menu from Excel: Data
- Import External Data - New Database Query the last menu that says FINISH
has an option to edit the query. If you select the edit option it opens MS
Query and allow you to view/modify the SQL.

When I program a VBA Excel query I usually start by recording a macro and
then maually edit the results of the macro and put it into a larger macro.
You can do the same thing and modify the SQL statment in the Excel Query as
needed.

 

[Mike]

Why not just do the query from excel like this.

Option Explicit
Private Sub getDataFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String, strConn
Dim iCol As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Path to data base\DatabaseName.mdb;Persist Security
Info=False"

sSQL = "Select Field1, Field2 From TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
iCol = 1
For Each fld In rs.Fields
Cells(1, iCol).Value = fld.Name
iCol = iCol + 1
Next fld
Cells(2, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

 

[Marc T]

Thanks for the tip Mike!

Is there any problem with passing parameters via the SQL that are stored in
cells in the Excel sheet?

Joel,
I can pass parameters, as above, to MS Query, but I suppose the problem is
how to pass from MS Query to Access (the Access query is the one requiring
the parameters).
If I can query Access directly using Mike's macro, this should bypass the
problem.

Marc

 

[Mike]

This passes a startdate and enddate that is stored in A1 and B1
Private Sub getDataFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String, strConn
Dim iCol As Long
Dim sStartDate, sEndDate As String

sStartDate = "#" & Range("A1").Value & "#"
sEndDate = "#" & Range("B1").Value & "#"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Ilsa\data\ilsa.mdb;Persist Security Info=False"

sSQL = "SELECT Sum(RegReconcile.Z_PAYOUTS_QTY) AS SumOfZ_PAYOUTS_QTY, " _
& "Sum(RegReconcile.CSHR_PAYOUTS) AS SumOfCSHR_PAYOUTS FROM
RegReconcile " _
& "INNER JOIN RegZCnt ON RegReconcile.REG_Z_COUNTER =
RegZCnt.REG_Z_COUNTER " _
& "WHERE (((RegZCnt.REG_Z_DATETIME)>=" & sStartDate & " And
(RegZCnt.REG_Z_DATETIME)<=" & sEndDate & "));"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
iCol = 1
For Each fld In rs.Fields
Cells(2, iCol).Value = fld.Name
iCol = iCol + 1
Next fld
Cells(3, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

 

[Marc T]

Thanks again Mike, should work a treat!

This passes a startdate and enddate that is stored in A1 and B1
Private Sub getDataFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String, strConn
Dim iCol As Long
Dim sStartDate, sEndDate As String

sStartDate = "#" & Range("A1").Value & "#"
sEndDate = "#" & Range("B1").Value & "#"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Ilsa\data\ilsa.mdb;Persist Security Info=False"

sSQL = "SELECT Sum(RegReconcile.Z_PAYOUTS_QTY) AS SumOfZ_PAYOUTS_QTY, " _
& "Sum(RegReconcile.CSHR_PAYOUTS) AS SumOfCSHR_PAYOUTS FROM
RegReconcile " _
& "INNER JOIN RegZCnt ON RegReconcile.REG_Z_COUNTER =
RegZCnt.REG_Z_COUNTER " _
& "WHERE (((RegZCnt.REG_Z_DATETIME)>=" & sStartDate & " And
(RegZCnt.REG_Z_DATETIME)<=" & sEndDate & "));"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
iCol = 1
For Each fld In rs.Fields
Cells(2, iCol).Value = fld.Name
iCol = iCol + 1
Next fld
Cells(3, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

 

[Joel]

Performing a query and open ing a database and reading the data is pretty
similar. either way works. The Command Text portion of the Query below are
the SQL statements

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;" & _
"DefaultDir=C:\TEMP;DriverId=25;" & _
"FIL=MS Access;MaxBufferSize=2048;PageTimeout"), _
Array("=5;")), Destination:=Range("A1"))

.CommandText = Array( _
"SELECT `0215 & 0064 parts received`.ID", _
"`0215 & 0064 parts received`.Program", _
"`0215 & 0064 parts received`.`Procurement Part Number`", _
"`0215 & 0064 parts received`.`Upscreen Part Number`", _
"`0215 & 0064 ", _
"parts received`.`Date Code`", _
"`0215 & 0064 parts received`.`Date Received`", _
"`0215 & 0064 parts received`.`Lot Review Date`", _
"`0215 & 0064 parts received`.`Drawing Revision`" & Chr(13) & ""
& Chr(10) & _
"FROM `C:\TEMP\Part Log`.'", _
"`0215 & 0064 parts received` `0215 & 0064 parts received`")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 

[Fraz frana]

Hi Mike, I used some of the parts of your code with my code, However i get
the error SQL Syntex error, Error 1004. and it highlights in yellow the very
last line of the code { .Refresh BackgroundQuery:=True}
I am not sure what I am doing wrong. If instead of using variable parameters
i use fixed parameteres the macro works fine. the following are two macros

WITH FIXED PARAMETERS (WORKS FINE)

Sub B08DATAGL_Query()
' MACRO TO RUN WITH FIXED CRITERIA WORKS JUST FINE
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'

'
Columns("A").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC",
Destination _
:=Range("A3"))
.CommandText = Array( _
"SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO,
GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM
SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE
(GLACC_PER.COM_CODE= 'B08') AND (GLACC_PER.PERIOD_NO<=200812) AND (GLACC_P" _
, "ER.CLOSE_BAL<>0)" & Chr(13) & "" & Chr(10) & "ORDER BY
GLACC_PER.PERIOD_NO DESC")
.Name = "DATAGLQUERY1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

WITH VARIABLE PARAMETERES GIVES ERRORS.

Sub B08DATAGL_Query_VARIABLE()
' MACRO TO RUN WITH VARIABLE CRITERIA DOES NOT WORK
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
Dim comcode As Variant
Dim transdate As Date

comcode = ActiveSheet.Range("E2").Text
transdate = ActiveSheet.Range("F2").Value




Columns("A").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC",
Destination _
:=Range("A3"))
.CommandText = Array( _
"SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO,
GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM
SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE
(((GLACC_PER.COM_CODE)=" & comcode & " AND (GLACC_PER.PERIOD_NO)<=" &
transdate & " AND (GLACC_P" _
, "ER.CLOSE_BAL<>0)));" & Chr(13) & "" & Chr(10) & "ORDER BY
GLACC_PER.PERIOD_NO DESC")
.Name = "DATAGLQUERY1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub


Your help will be much appreciated..
thanks (e-mail address removed)

 

[Joel]

Towe closing parenbthesis where in the wrong spot and you may need the two
single quotes. Below is code that wiull make your original code that worked
equivalent to new code with two variables. I broke the original strings into
multiple strings so I wouldn't get an error in posting the code (lines too
long) and to make it easier to see the individual pieces of the command lines.

comcode = "B08"
transdate = 200812

Columns("A").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;" & _
"DSN=MABODBC;" & _
"DB=TSCFIN;" & _
"SRVR=SERVER1;" & _
"UID=MABODBC;" & _
"PWD=MABODBC", _
Destination:=Range("A3"))

.CommandText = Array( _
"SELECT GLACC_PER.COM_CODE," & _
"GLACC_PER.PERIOD_NO," & _
"GLACC_PER.CLOSE_BAL," & _
"GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & _
"FROM SYSADM.GLACC_PER GLACC_PER" & _
Chr(13) & "" & Chr(10) & _
"WHERE(((GLACC_PER.COM_CODE= '" & comcode & _
"') AND (GLACC_PER.PERIOD_NO<=" & transdate & _
") AND (GLACC_P", "ER.CLOSE_BAL<>0)));" & _
Chr(13) & "" & Chr(10) & _
"ORDER BY GLACC_PER.PERIOD_NO DESC")