Assist to run Access parameter query from VBA or ADO

[Ken]

Hi,

Below is the code for creating table from data queried by prompting user to enter customer id.

If Customer exist, create data history of customer.

Planning to run this query via VBA using ADO or VBA coding with already provided customer id through a variable assigned from excel cell.

Problem: While running the query via VBA code "QryCreateTbl_Test", it prompts for cust Id. Where to enter parameter in below VBA code or ADO connection

Looking for solution: Removing prompt for user entering customer id and gets value from an excel cell to a variable for running the query.


Where/How to run ACCESS query with parameter in VBA or by using ADO

Sub MakeDataTable()
    Dim Appl As Object
    Dim sCustId As String
    
    Set Appl = CreateObject("Access.Application")
        Appl.openCurrentDatabase ("Test.mdb")
        
        Appl.Visible = True
        Appl.DoCmd.SetWarning = True
        
        Appl.DoCmd.OpenQuery "QryCreateTbl_Test", acViewNOrmal, acReadonly
        
        Appl.DoCmd.SetWarning = False
        App.Quit
    
End Sub

 

[dhookom]

I would build an SQL statement dynamically with an embedded customer id. Then execute the SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[Ken]

Hi dhookom,

Currently, planning to keep all SQL in Access Query and run through VBA.

TechIT

 

[dhookom]

If you want to save all of your queries, you can use a little DAO or ADO code to change the SQL of a saved query.

Duane
Hook'D on Access
MS Access MVP

 

[bubba100]

I have used this method in the past. This is 'a' way. Duane's suggestion is better.
I have not given you everything so some of this won't make sense.

Dim strName As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim RST1 As DAO.Recordset
Dim RSRepAud As DAO.Recordset
Dim RSOnRoad As DAO.Recordset
Dim RSBulk As DAO.Recordset
Dim DestFile As String
Dim bRunning As Boolean
Dim ExcelWasRunning As Boolean
Dim Message
Dim FileName As String
Dim strSheetName, strVar As String
Dim sheet As Object
Dim AuditPeriod As Variant
Dim CurrentField As Variant
Dim strProperty As String
Dim rstFUTS As DAO.QueryDef
Dim RSRep As DAO.QueryDef
Dim RsRoad As DAO.QueryDef
Dim RsBulkWith As DAO.QueryDef
Dim prm As Parameter
strSheetName = "Greater_of_Fuel"
Set db = CurrentDb
Set rstFUTS = db.QueryDefs("qryFuts")
Set RST1 = rstFUTS.OpenRecordset(dbOpenSnapshot)
Forms!OtherAppl!txtFUTSother.Value = RST1!Futs_No
Set RSRep = db.QueryDefs("qryIFTARep_Aud_JoinOnRoadBulk")
For Each prm In RSRep.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RsRoad = db.QueryDefs("qryNaReptdOnRoad")
For Each prm In RsRoad.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RsBulkWith = db.QueryDefs("qryNaReptdBulk")
For Each prm In RsBulkWith.Parameters
prm.Value = Eval(prm.Name)
Next prm

 

[Ken]

Hi All,

Thank you so much for your help, solved with below code.

dim aConn as adodb.connection
dim aRS   as adodb.recordset

dim aComm as adodb.command
dim pTxt  as adodb.parameter

dim pName as string
dim qPrompt as string

set aConn = new adodb.connection
set aRS   = New adodb.Recordset

set aComm = new adodb.command
set aComm.activeConnection = adoConn

aComm.CommandText = "qry_Access1"
pName = "Enter Cust_Id"
qPrompt ="C0001"

' adodb connection 
aConn.open " ......"

set pTxt = aComm.CreateParameter(pName, adBStr, adParamInput,,qPrompt)
aComm.Parameters.append pTxt

set aRS  = aComm.execute

Thanks,
TechIT