Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Access queries from Excel
I have an Excel sheet that I want to connect to several different Access
queries. A lot can be done automaticaly with importing data. What I want to do is to add a parameter to the query that is being executed. The VB code looks like this: ..CommandText = Array("SELECT * FROM `C:\PaycheckData`.`3-2CurBMProfile`") I want to add CompanyID (which the user choose in the Excel doc. The Queries have a bunch of " in them so sending the SQL in is hard, and I don't want to do it like that either, because there are gonna be non-programmer users using the document. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Access queries from Excel
Hello,
I don't know if I understand your question correctly, and I'm not familliar with the code you are showing below. I used connections from Excel to Access in the past by using Querydefs, Recordsets or Tabledefs in VB. When doing so you can create whatever SQL statement you want including whatever parameters you'd like in a text string. It's true that the " can be confusing when writing the code, but once finished the user will never see this. Michel "smartchick" wrote: I have an Excel sheet that I want to connect to several different Access queries. A lot can be done automaticaly with importing data. What I want to do is to add a parameter to the query that is being executed. The VB code looks like this: .CommandText = Array("SELECT * FROM `C:\PaycheckData`.`3-2CurBMProfile`") I want to add CompanyID (which the user choose in the Excel doc. The Queries have a bunch of " in them so sending the SQL in is hard, and I don't want to do it like that either, because there are gonna be non-programmer users using the document. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Access queries from Excel
Suppose the field you want to specify as a criterion is F1. Also suppose the
value of that field is in cell B2. Then, you could modify the code to the untested .CommandText = Array("SELECT * FROM `C:\PaycheckData`.`3-2CurBMProfile` WHERE F1=" & activesheet.cells(2,2).value) I don't know if F1 has to be prefaced with the data set name, i.e., should it be ...WHERE `3-2CurBMProfile.F1`="... You will have to check on that. "smartchick" wrote: I have an Excel sheet that I want to connect to several different Access queries. A lot can be done automaticaly with importing data. What I want to do is to add a parameter to the query that is being executed. The VB code looks like this: .CommandText = Array("SELECT * FROM `C:\PaycheckData`.`3-2CurBMProfile`") I want to add CompanyID (which the user choose in the Excel doc. The Queries have a bunch of " in them so sending the SQL in is hard, and I don't want to do it like that either, because there are gonna be non-programmer users using the document. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Access queries from Excel
Here is some code that I use...
Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _ ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean) As ADODB.Recordset Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " & strOrderBy, strConnection, , , adCmdText If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing End Function It returns either a connected or disconnected recordset depending on the value of the final argument. Here is how it is used... Dim strBranch As String Dim strSelect As String Dim strFrom As String Dim strWhere As String Dim strOrderBy As String Dim rst As ADODB.Recordset 'Create SQL statement strSelect = "SELECT tblFixedAssets.[Update ID], tblFixedAssets.[Creation Date], tblFixedAssets.Active, tblFixedAssets.Account, tblFixedAssets.Period, tblFixedAssets.Cost, tblFixedAssets.Description, tblFixedAssets.Reason" strFrom = "FROM tblFixedAssets" strWhere = "WHERE (((tblFixedAssets.[Update ID])=""" & shtFixedAssets.Range("AB" & m_PreviousRow).Value & """) AND ((tblFixedAssets.[Creation Date])=#" & shtFixedAssets.Range("AC" & m_PreviousRow).Value & "#))" strOrderBy = ";" Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True) This gets me an updateable record set, based on the values in a number of cells in a sheet named shtFixedAssets. HTH... "smartchick" wrote: I have an Excel sheet that I want to connect to several different Access queries. A lot can be done automaticaly with importing data. What I want to do is to add a parameter to the query that is being executed. The VB code looks like this: .CommandText = Array("SELECT * FROM `C:\PaycheckData`.`3-2CurBMProfile`") I want to add CompanyID (which the user choose in the Excel doc. The Queries have a bunch of " in them so sending the SQL in is hard, and I don't want to do it like that either, because there are gonna be non-programmer users using the document. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Access queries from Excel
smartchick wrote:
.CommandText = Array("SELECT * FROM `C:\PaycheckData`.`3-2CurBMProfile`") I want to add CompanyID (which the user choose in the Excel doc. See: http://www.dicks-clicks.com/excel/Ex...htm#Parameters Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open all Excel spreadsheets/fles before running queries | Excel Discussion (Misc queries) | |||
Access Queries not seen in Excel | Excel Discussion (Misc queries) | |||
Access Queries into Excel | Excel Worksheet Functions | |||
Excel Queries using Access data | Excel Discussion (Misc queries) | |||
Using Excel Worksheets like Access Queries | Links and Linking in Excel |