Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
I have a worksheet that I need to update on a daily basis
with data from an external Oracle database. I am using the following query which gets yesterday's data (the Oracle data is always for the previous day). SELECT Sum(recno) FROM mailboxes WHERE (trunc(logdate)=trunc(sysdate-1)) This sums the values in the rec_no column for all rows that were logged yesterday. The thing is that on a Monday, I need to get the data for both Saturday and Sunday so would need to be able to pass the date as a parameter rather than having to manually change the query. How does one do this? Many thanks, Seamus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
If I were passing to SQL Server then I would pass it in as a string
something like; SELECT SUM(fld_hours) AS SumHours FROM tbl_techtime WHERE (fld_time_started = CONVERT(DATETIME, ' & mydate & ', 102)) SELECT SUM(fld_hours) AS SumHours FROM tbl_techtime WHERE (fld_time_started = CONVERT(DATETIME, ' & mystartdate & ', 102) AND fld_time_started <= CONVERT(DATETIME, ' & myenddate & ', 102)) SELECT SUM(fld_hours) AS SumHours FROM tbl_techtime WHERE (fld_time_started BETWEEN CONVERT(DATETIME, ' & mystartdate & ', 102) AND CONVERT(DATETIME, ' & myenddate & ', 102)) No idea really with oracle. -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Seamus Conlon" wrote: |I have a worksheet that I need to update on a daily basis | with data from an external Oracle database. I am using | the following query which gets yesterday's data (the Oracle | data is always for the previous day). | | SELECT Sum(recno) FROM mailboxes | WHERE (trunc(logdate)=trunc(sysdate-1)) | | This sums the values in the rec_no column for all rows | that were logged yesterday. The thing is that on a Monday, | I need to get the data for both Saturday and Sunday so | would need to be able to pass the date as a parameter | rather than having to manually change the query. | | How does one do this? | | Many thanks, | Seamus | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
How are you executing the query (SQL statement queried to a recordset in
Excel VBA, other?)? "Seamus Conlon" wrote: I have a worksheet that I need to update on a daily basis with data from an external Oracle database. I am using the following query which gets yesterday's data (the Oracle data is always for the previous day). SELECT Sum(recno) FROM mailboxes WHERE (trunc(logdate)=trunc(sysdate-1)) This sums the values in the rec_no column for all rows that were logged yesterday. The thing is that on a Monday, I need to get the data for both Saturday and Sunday so would need to be able to pass the date as a parameter rather than having to manually change the query. How does one do this? Many thanks, Seamus |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
"Bill Pfister" wrote in message
... How are you executing the query (SQL statement queried to a recordset in Excel VBA, other?)? To insert the query in the worksheet I initially used the New Database Query option under Import External Data. To refresh it I have a macro that uses something like: Selection.QueryTable.Refresh Thanks, Seamus |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Using VBA, you can craft your SQL statement to contain whatever parameters
you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub "Seamus Conlon" wrote: "Bill Pfister" wrote in message ... How are you executing the query (SQL statement queried to a recordset in Excel VBA, other?)? To insert the query in the worksheet I initially used the New Database Query option under Import External Data. To refresh it I have a macro that uses something like: Selection.QueryTable.Refresh Thanks, Seamus |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Bill, I modified your code slightly to suit and when I run
it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Seamus, would you mind dumping the connection string to the debug window and
posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
The output from the interactive pane is below:
?activesheet.querytables(1).connection ODBC;DRIVER={Microsoft ODBC for Oracle};UID=seamus;PWD=xfgtwy;SERVER=animotroi; This is for the query I set up manually and it works fine. So, in your code I put strConn = "ODBC;DRIVER={Microsoft ODBC for Oracle};UID=seamus;PWD=xfgtwy;SERVER=animotroi;" strSQL="SELECT Sum(daily_mailboxes.REC_NO)" & strSeparator & _ "FROM daily_mailboxes " I left the following code as is: With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With I haven't tried anything further yet. Seamus "Bill Pfister" wrote in message ... Seamus, would you mind dumping the connection string to the debug window and posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Sorry, forgot to say that I do have an ODBC connection set up and it works fine for external linking of the database within Access. Seamus "Bill Pfister" wrote in message ... Seamus, would you mind dumping the connection string to the debug window and posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Seamus, sorry for getting to this earlier, but you can simply change the SQL
statement of a previously established QueryTable. The following example modifies the Where clause for the QueryTable that begins in cell C6. Public Sub TestRefresh() Dim strSQL As String Dim strSeparator As String Dim strFilename As String strFilename = "D:\Data\Test.mdb" strSeparator = Chr(13) & "" & Chr(10) ' first query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh ' need to wait while the first query refreshes application.wait timeserial(hour(now()),minute(now()),second(now()+ 5)) ' second query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name = 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh End Sub "Seamus Conlon" wrote: Sorry, forgot to say that I do have an ODBC connection set up and it works fine for external linking of the database within Access. Seamus "Bill Pfister" wrote in message ... Seamus, would you mind dumping the connection string to the debug window and posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Thanks for the tip and I have tried to use it but still have a
problem when including the date parameter. My SQL is strsql = "SELECT Sum(REC_NO) " & strSeparator & _ "FROM daily_mailboxes " & strSeparator & _ "WHERE trunc(logdate) = '17-09-2006'" Range("B10").QueryTable.CommandText = Array(strSQL) Range("B10").QueryTable.Refresh This gives a general ODBC error. When I use the following WHERE clause it is ok. "WHERE trunc(logdate) = trunc(sysdate)" I really want to have a variable date, but I can't even get a literal one to work. Seamus "Bill Pfister" wrote in message ... Seamus, sorry for getting to this earlier, but you can simply change the SQL statement of a previously established QueryTable. The following example modifies the Where clause for the QueryTable that begins in cell C6. Public Sub TestRefresh() Dim strSQL As String Dim strSeparator As String Dim strFilename As String strFilename = "D:\Data\Test.mdb" strSeparator = Chr(13) & "" & Chr(10) ' first query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh ' need to wait while the first query refreshes application.wait timeserial(hour(now()),minute(now()),second(now()+ 5)) ' second query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name = 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh End Sub "Seamus Conlon" wrote: Sorry, forgot to say that I do have an ODBC connection set up and it works fine for external linking of the database within Access. Seamus "Bill Pfister" wrote in message ... Seamus, would you mind dumping the connection string to the debug window and posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Use "#" for date qualifiers in your SQL statement (instead of single quotes)
- see the included example. Try returning a a simple Select query ("SELECT REC_NO FROM daily_mailbox") to determine if you're hitting the connection properly. strSQL = "SELECT Sampler.* " & _ "FROM Sampler " & _ "WHERE Sampler.When #1/1/2004#" "Seamus Conlon" wrote: Thanks for the tip and I have tried to use it but still have a problem when including the date parameter. My SQL is strsql = "SELECT Sum(REC_NO) " & strSeparator & _ "FROM daily_mailboxes " & strSeparator & _ "WHERE trunc(logdate) = '17-09-2006'" Range("B10").QueryTable.CommandText = Array(strSQL) Range("B10").QueryTable.Refresh This gives a general ODBC error. When I use the following WHERE clause it is ok. "WHERE trunc(logdate) = trunc(sysdate)" I really want to have a variable date, but I can't even get a literal one to work. Seamus "Bill Pfister" wrote in message ... Seamus, sorry for getting to this earlier, but you can simply change the SQL statement of a previously established QueryTable. The following example modifies the Where clause for the QueryTable that begins in cell C6. Public Sub TestRefresh() Dim strSQL As String Dim strSeparator As String Dim strFilename As String strFilename = "D:\Data\Test.mdb" strSeparator = Chr(13) & "" & Chr(10) ' first query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh ' need to wait while the first query refreshes application.wait timeserial(hour(now()),minute(now()),second(now()+ 5)) ' second query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name = 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh End Sub "Seamus Conlon" wrote: Sorry, forgot to say that I do have an ODBC connection set up and it works fine for external linking of the database within Access. Seamus "Bill Pfister" wrote in message ... Seamus, would you mind dumping the connection string to the debug window and posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing parameter to a select query
Thanks for all the help Bill. It is working now with the date format you suggested. Seamus "Bill Pfister" wrote in message ... Use "#" for date qualifiers in your SQL statement (instead of single quotes) - see the included example. Try returning a a simple Select query ("SELECT REC_NO FROM daily_mailbox") to determine if you're hitting the connection properly. strSQL = "SELECT Sampler.* " & _ "FROM Sampler " & _ "WHERE Sampler.When #1/1/2004#" "Seamus Conlon" wrote: Thanks for the tip and I have tried to use it but still have a problem when including the date parameter. My SQL is strsql = "SELECT Sum(REC_NO) " & strSeparator & _ "FROM daily_mailboxes " & strSeparator & _ "WHERE trunc(logdate) = '17-09-2006'" Range("B10").QueryTable.CommandText = Array(strSQL) Range("B10").QueryTable.Refresh This gives a general ODBC error. When I use the following WHERE clause it is ok. "WHERE trunc(logdate) = trunc(sysdate)" I really want to have a variable date, but I can't even get a literal one to work. Seamus "Bill Pfister" wrote in message ... Seamus, sorry for getting to this earlier, but you can simply change the SQL statement of a previously established QueryTable. The following example modifies the Where clause for the QueryTable that begins in cell C6. Public Sub TestRefresh() Dim strSQL As String Dim strSeparator As String Dim strFilename As String strFilename = "D:\Data\Test.mdb" strSeparator = Chr(13) & "" & Chr(10) ' first query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh ' need to wait while the first query refreshes application.wait timeserial(hour(now()),minute(now()),second(now()+ 5)) ' second query strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name = 'Administrative' " Range("C6").QueryTable.CommandText = Array(strSQL) Range("C6").QueryTable.Refresh End Sub "Seamus Conlon" wrote: Sorry, forgot to say that I do have an ODBC connection set up and it works fine for external linking of the database within Access. Seamus "Bill Pfister" wrote in message ... Seamus, would you mind dumping the connection string to the debug window and posting it? Try using the macro recording to nail down the exact syntax. Do you have an ODBC connection for your database? In my test case, I actually created an ODBC connection (in the Control Panel / Administrative Tools / Data Sources tool) before running recording the action. That may have an effect. Bill "Seamus Conlon" wrote: Bill, I modified your code slightly to suit and when I run it I get an error "Invalid procedure call or argument" at the first 'With ActiveSheet.QueryTables.Add' line. I got the connection string from the existing query so I expect it is correct. Any ideas on what is wrong? Seamus "Bill Pfister" wrote in message ... Using VBA, you can craft your SQL statement to contain whatever parameters you need. In the following example, I'm pointing to a local Access database, you would only have to modify the details (driver, etc.) in the connect string to point to Oracle or another DBMS. Sub AccessQueryData1() Dim strFilename As String Dim strSQL As String Dim strConn As String Dim strSeparator As String strSeparator = Chr(13) & "" & Chr(10) strFilename = "D:\Data\Test.mdb" strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" strSQL = "SELECT Category.Name" & strSeparator & _ "FROM `" & strFilename & "`.Category Category " & strSeparator & _ "WHERE Category.Name < 'Administrative' " ' Focus on only the parameter With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) End With ' All the other parameters With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("C6")) .CommandText = Array(strSQL) .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 Range("E11").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing parameter to a ms query | Excel Programming | |||
Passing a Sub's name as parameter | Excel Programming | |||
Passing parameter to a query | Excel Programming | |||
?Passing argument/parameter | Excel Programming | |||
?Passing argument/parameter | Excel Programming |