Copy data from one db to another using 'Select * Into... From... IN...' suddenly giving RT err 3251

[DAY4]

I wrote a sub last year to copy tables from an archived backend into the live database.
It has been working fine for the past 10 months but now all of a sudden is giving me a Run-Time error '3251': Operation is not supported for this type of object.



Here is a bit of test code that I set up in a new database and I get the same error.

Sub test()


    Dim dbPath As String
    Dim tblName As String
    
    dbPath = "S:\DataHome\BidBunker_DATA.accdb"
    tblName = "tblBookmarks"
    
    DoCmd.RunSQL "SELECT * INTO newTable FROM " & tblName & " IN '" & dbPath & "'"


End Sub

The fact that I am getting the error in a brand new database seems to indicate that some sort of office update is to blame but I am not sure how to verify that. I am using office 365.
any ideas? thanks in advance

[DAY4]

I just copied the sql text directly into a new query design SQL mode and it also gives the same result. So it's got to be an office update. right?

[orange]

You could try adjusting the SQL. Seems tblName would be a string, so you may need single quotes
DoCmd.RunSQL "SELECT * INTO newTable FROM '" & tblName & "' IN '" & dbPath & "'"

[isladogs]

I use similar code regularly without issues and I have the current version 2102 of Access 365 .... so I think you can probably discount that as an issue

Anyway, as a test, I used your exact code & it worked fine ... even when I had the the source table open in the source db

You could try changing the last line to the more efficient version

CurrentDb.Execute "SELECT * INTO newTable FROM " & tblName & " IN '" & dbPath & "'", dbFailOnError

However, if newTable already exists, you will get error 3010

Check what happens if you import the table using the External Data ribbon item

[DAY4]

Thanks Orange, I did try that and I get a syntax error. Plus I know that it was working before with no changes for several months.

[DAY4]

Thanks isladogs, I appreciate the fast and thorough answer. however with your suggested code I am getting the exact same error. I called my brother and and had him test my code as well and it worked for him. (he's not a developer but he has the full office suite on his machine)

So it has to be something wrong on my computer but I am not sure where to look. Supposing it was an office update that I got how would I go about reverting back?

[DAY4]

I also forgot to mention in the original post that using the External Data Ribbon works fine as you might expect, so, I'm not sure what that means.

[Micron]

something wrong with your attachment link ("invalid")

[DAY4]

Sorry, It didn't have any real new information. It was just a screen shot of the error pop up with the suggested changes from isladogs

[isladogs]

I also got an error with your attachment. If you can try and upload the file, I'll be happy to test it on my workstation.

You haven't said which version of A365 you have though I still doubt that is the issue.
There is info online about how to revert to an earlier version. I've done it myself but don't have the details to hand

The single quotes are definitely not correct in this type of code. It sometimes helps to use this syntax
... FROM [" & tblName & "] ...
The [] brackets are essential if the table name contains a space or special character...but that isn't the case here

Personally, I'd recommend that you don't create a new table each time.
Instead use an INSERT (append) query to add new records. If necessary, use a DELETE query to empty the table beforehand.

[Micron]

It was just a screen shot of the error pop up

Really no need to zip pics and frankly, it's an unnecessary pain in the butt. There is an "insert image" button on the toolbar.

[DAY4]

So... researching my Office version I found out that I am an Office Insider (That's probably a mistake) meaning I get new builds of Office weekly on their Beta Channel. I messaged them to get some support but I have not gotten a response back yet. Just letting everyone here know that I am suspecting some sort of beta version bug. Is there anyone else here that gets regular insider updates that can confirm they have the same issue, or that they are not having the issue?

[DAY4]

There is an "insert image" button on the toolbar.

right, I used the insert image button and I could see it just fine. but after you mentioned the error, I just removed it so that no one would be confused. because like I said there wasn't any real new information in the image.

[DAY4]

Personally, I'd recommend that you don't create a new table each time.
Instead use an INSERT (append) query to add new records. If necessary, use a DELETE query to empty the table beforehand.

So a little more background info. This is a large database. the other db file that I am copying from contains archived data that I don't need very often, only in certain cases. there are already 50+ tables in the live DB so I use VBA-SQL to copy the data in run the query then delete the table when we are done.

I agree that appending is preferable most of the time. (and maybe that is the solution) I was trying to avoid having 20 empty tables just sitting there in the file. I haven't even tried that yet so I'll let you know if it even works.

Thanks again for all the suggestions.

[DAY4]

Personally, I'd recommend that you don't create a new table each time.
Instead use an INSERT (append) query to add new records. If necessary, use a DELETE query to empty the table beforehand.

So I tried it and I get the same error:

INSERT INTO newTable SELECT * FROM tblBookmarks IN 'S:\DataHome\BidBunker_DATA.accdb';

so it's not the creating of a new table that is the issue, it somehow has to do with referencing the other database...