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

Question

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)

I'm not an expert in Access, but... If you try storing your Range("ASOFDATE").Value in a variable first and then you send the variable? I'm not sure if you can send an array as variable. — user8753746
– user8753746, Commented Oct 30, 2017 at 21:13
What's this piece of code: Conn.Execute (sql) doing? Where are you getting the error? — Erik A
– Erik A, Commented Oct 30, 2017 at 21:21
And is your "AS OF DATE" field really an adInteger type, or is it an adDate type? (That's probably not causing your error though.) — YowE3K
– YowE3K, Commented Oct 30, 2017 at 21:37
Sorry for not clarifying the setup better. The name of the MT Query is 'c1GetLIVEDBnTF' and it inserts all data into a table named 'bLIVEDBnTF'. — ASH
– ASH, Commented Oct 30, 2017 at 21:38
You can't SELECT from a Make Table query. You have to make the table, then select from the table you made. (Or change the make table query to just be a select query.) P.S. I don't know enough ADODB to write an answer without a lot of testing, so someone else can feel free to post a proper answer. — YowE3K
– YowE3K, Commented Oct 30, 2017 at 21:39

ASH · Accepted Answer · 2017-11-04 02:22:45Z

0

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

answered Nov 4, 2017 at 2:22

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related