Insert into external table using vba

[jamesf248]

Can anyone tell me why this does not work ?

Thanks in advanced

Dim SQL As String

SQL = "INSERT INTO tblcategory ( cat_name ) IN 'C:\cmr\office.mdb'"
SQL = SQL & " SELECT tblcategory.cat_name"
SQL = SQL & " FROM tblcategory;"

 

[ByteMyzer]

Try:

Dim SQL As String

SQL = "INSERT INTO [;DATABASE=C:\cmr\office.mdb].tblcategory ( cat_name ) "
SQL = SQL & " SELECT tblcategory.cat_name"
SQL = SQL & " FROM tblcategory;"

 

[jamesf248]

Thanks, but still no luck?

 

[ByteMyzer]

Since you have apparently posted only part of your code, we have no way to know if you are actually doing anything with the variable SQL. Also, since you only say:

Can anyone tell me why this does not work ?

...we have no way to know if it isn't doing anything, if it is trying to do something but only returning an error message, and if it is returning an error message, what that message is. Could you please provide more specifics?

 

[jamesf248]

Having noticed a typo I am now getting Runtime Error 3134 error in INSERT INTO statement.

 

[ByteMyzer]

You know, if you keep doing this, you make it impossible for us to help you. You continue to post PART of the problem without giving us enough information.

I replicated the scenario based on the information you gave and got no such error; it worked fine. If it isn't working for you, there's something ELSE you haven't told us yet.

Would it be possible for you to post the ENTIRE code in question so we can see what's going on and be better able to help you troubleshoot the problem? For instance, you didn't tell us the typo you fixed. For all we know, the "fix" might have created a new problem, but we wouldn't know that without seeing it, now would we?

 

[jamesf248]

All fixed now

Thanks for your help

James