How can I pass an Excel variable to an Access query

R

R Oschger

I have an Access database that contains the following fields: Year/Mo,
Product Category, Part number. The data consists of transactions for a
rolling 12 month period and is updated monthly. I am running queries against
this data base from an Excel application. Both applications are housed on a
single system.

I have an Excel variable that contains a Year/Mo value that I would like to
pass to an
Access query as a limiting criteria. Is it possible to do this? How?
 
W

Warrio

If you variable is stored into an excel File, you can connect your excel
file to Access as a linked table
for that go to the database window into Access, Tables. Menu File, Get
External Data, Link tables.. and choose into Files of type : "Microsoft
Excel *.xls" then get your excel file!
and then you can access your xls cells as a normal access table.

is this what you want to do?
 
B

Brendan Reynolds

Below is the SQL for a saved parameter query in Access ...

PARAMETERS [Last Name?] Text ( 50 );
SELECT Employees.*
FROM Employees
WHERE (((Employees.LastName)=[Last Name?]));

.... and here is some VBA code to pass a variable as the parameter value, and
retrieve the result. This code would work identically from Access, Excel,
Word, or any other VBA-enabled application.

Public Function GetFullName(LastName As String) As String

Dim strConnection As String
Dim objConnection As ADODB.connection
Dim objCommand As ADODB.Command
Dim objParam As ADODB.Parameter
Dim rst As ADODB.Recordset

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\USENET\Test.mdb;" & _
"Persist Security Info=False"
Set objConnection = New ADODB.connection
objConnection.Open strConnection
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "qryTest"
objCommand.CommandType = adCmdStoredProc
Set objParam = objCommand.CreateParameter("[Last Name?]", _
adVarChar, adParamInput, 50, LastName)
objCommand.Parameters.Append objParam
Set rst = objCommand.Execute
GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
objConnection.Close

End Function

Result in the Excel VBA Immediate window ...
? getfullname("Davolio")
Davolio, Nancy

This code requires a reference (Tools, References in the VBA editor) to the
Microsoft ActiveX Data Objects 2.x Library. If you're more familiar with
DAO, the code is easily adapted to use DAO instead.
 
R

R Oschger

Thanks, I will give it a try

Warrio said:
If you variable is stored into an excel File, you can connect your excel
file to Access as a linked table
for that go to the database window into Access, Tables. Menu File, Get
External Data, Link tables.. and choose into Files of type : "Microsoft
Excel *.xls" then get your excel file!
and then you can access your xls cells as a normal access table.

is this what you want to do?
Click to expand...
 
R

R Oschger

Thanks, I will try to set this one up in my code.
Brendan Reynolds said:
Below is the SQL for a saved parameter query in Access ...

PARAMETERS [Last Name?] Text ( 50 );
SELECT Employees.*
FROM Employees
WHERE (((Employees.LastName)=[Last Name?]));

.... and here is some VBA code to pass a variable as the parameter value, and
retrieve the result. This code would work identically from Access, Excel,
Word, or any other VBA-enabled application.

Public Function GetFullName(LastName As String) As String

Dim strConnection As String
Dim objConnection As ADODB.connection
Dim objCommand As ADODB.Command
Dim objParam As ADODB.Parameter
Dim rst As ADODB.Recordset

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\USENET\Test.mdb;" & _
"Persist Security Info=False"
Set objConnection = New ADODB.connection
objConnection.Open strConnection
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "qryTest"
objCommand.CommandType = adCmdStoredProc
Set objParam = objCommand.CreateParameter("[Last Name?]", _
adVarChar, adParamInput, 50, LastName)
objCommand.Parameters.Append objParam
Set rst = objCommand.Execute
GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
objConnection.Close

End Function

Result in the Excel VBA Immediate window ...
? getfullname("Davolio")
Davolio, Nancy

This code requires a reference (Tools, References in the VBA editor) to the
Microsoft ActiveX Data Objects 2.x Library. If you're more familiar with
DAO, the code is easily adapted to use DAO instead.

--
Brendan Reynolds


R Oschger said:
I have an Access database that contains the following fields: Year/Mo,
Product Category, Part number. The data consists of transactions for a
rolling 12 month period and is updated monthly. I am running queries
against
this data base from an Excel application. Both applications are housed on
a
single system.

I have an Excel variable that contains a Year/Mo value that I would like
to
pass to an
Access query as a limiting criteria. Is it possible to do this? How?
Click to expand...
Click to expand...
 
Top