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.

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.

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.

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.

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.

--