Solved Access - INSERT INTO w/ strPath

[EzGoingKev]

I have some code that sets a file location as strPath.

I am trying to use it to append some data from a table in another database to a local table in the db I am working in. I have used:

DoCmd.RunSQL "INSERT INTO BrakeTypeWorking SELECT * FROM BrakeType IN strPath" which gives me a Run-time error 3024: Could not find the file 'C\Users\MyName\Desktop\strPath".

It is not picking up the strPath file name. I am assuming because it is because of the quotes. I have tried it few other ways but nothing seems to work.

Can anyone please advise on what I need to do to get it to run?

 

[Ranman256]

link the table as an external tbl
run append query.

 

[Pat Hartman]

You need to get VBA to expand the contents of strPath and concatenate the value to the string rather than the name of the variable.

DoCmd.RunSQL "INSERT INTO BrakeTypeWorking SELECT * FROM BrakeType IN '" & strPath & "'"

I enclosed the contents of strPath in single quotes in case there are any embedded characters that Access doesn't like. You still may have trouble because Access didn't get the memo and so in some places it balks at file names that include more than one "dot". I don't ever use this method. I always use links so I don't know if this is one of those problem cases.