CODEWITHSUNDEEP
excelms-accessvba
ASH
ASH

Reputation: 20302

How to Pass a Parameter from an Excel Sheet into an Access Make Table Query

I'm opening an ADODB connection using a small function (this works fine) and I'm trying to pass a parameter into a Make Table Query. and getting an error that reads: 'an action query can not be used as a row source'

Here is the code that I'm testing.

Conn.Execute (sql)

With cmd
    .ActiveConnection = Conn
    .CommandType = adCmdText
    .CommandText = "SELECT * FROM c1GetLIVEDBnTF WHERE [AS OF DATE] = ?"
End With

cmd.Parameters.Append cmd.CreateParameter("[AS OF DATE]", adInteger, adParamInput, 10)
cmd.Parameters(0).Value = Range("ASOFDATE").Value

Set rs = cmd.Execute

I'm trying to pass a date from Range("ASOFDATE").Value (Excel worksheet) into an Access Make Table Query.

I found the example below, but couldn't get to to work.

Pass VBA Variable into Access Query(Excel VBA)

Upvotes: 0

Views: 222

Answers (1)

ASH
ASH

Reputation: 20302

I finally got this to work.

Sub RunAccessQueries()

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset

GoHere = ThisWorkbook.Path

ASOFDATE = Format(Range("ASOFDATE").Value, "MM/DD/YYYY")

Set ws = DBEngine.Workspaces(0)
Set db = DBEngine.OpenDatabase(GoHere & "\Main.mdb", False, False, "MS Access;PWD=pass")

sql = "INSERT INTO etc.;"
db.Execute sql, dbFailOnError
RecordsUpdated = db.RecordsAffected

db.Close

End Sub

The solution came form here.

Error 3219- Invalid Operation

Upvotes: 0

Related Questions