Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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. "Marc T" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
Thanks again Mike, should work a treat!
"Mike" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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(Arra y( _ "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 "Marc T" wrote: Thanks again Mike, should work a treat! "Mike" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Excel to Access - Passing Parameters?
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:D").Select Selection.ClearContents With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABOD BC;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:D").Select Selection.ClearContents With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABOD BC;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 "Mike" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run an Access Query from Excel VBA with Parameters | Excel Programming | |||
help with passing date to access as parameter query(code included) | Excel Programming | |||
Passing Parameters to Word from Excel | Excel Programming | |||
Passing parameters from excel to access | Excel Programming | |||
Passing Excel Objects As Parameters | Excel Programming |