Having problems w/Make-Table query using ADO

[tsosiel]

Grettings. I hope someone can help me understand what I'm doing wrong!!! I have the following code:
1) tblMainTable is a linked table
2) I want to copy tblMainTable to tblMyTable - both are in MyOwnDB.mdb (can I use CurrentProject instead??)

Public Sub MakeMyTable()

Dim cnnMine As New ADODB.Connection
Dim strMine As String
Dim strMyTable As String

strCreateTable = "SELECT * INTO tblMyTable " _
& " FROM tblMainTable;"

cnnMine.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\MyOwnDB.mdb;"
cnnMine.Execute (strCreateTable)
cnnMine.Close

End Sub

Every time I try to execute this I get the following error: The Microsoft Jet database engine stopped the process becuase you and another user are attempting to change the same data at the same time.

I've tried to understand this, but after 8 hours of this - it's frustrating. I thank you in advance for any assistance.

- Lorentz

 

[mikevh]

I modified your code slightly and it worked for me.
I'm just learning ADO myself, but I think the difficulty
was the "cnnMine.Open". I got a different error message
than you report when I tried it, but the general gist was
the same. Access seems to think you have the data source
open already and is protesting the attempt to open it again.
The code below works (I added a little error handling, took
out strMine and strMyTable which were never referenced,
and declared strCreateTable, and yes, I'm using CurrentProject.Connection):

Public Sub MakeMyTable()
On Error GoTo HandleError

Dim cnnMine As ADODB.Connection
Dim strCreateTable As String

Set cnnMine = CurrentProject.Connection
strCreateTable = "SELECT * INTO tblMyTable " _
& " FROM newtext;"

cnnMine.Execute (strCreateTable)
cnnMine.Close

ExitHere:
Exit Sub

HandleError:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ExitHere

End Sub

 

[tsosiel]

I ran the code and it gave me the same error: Error -2147467259 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

Question: The table I want to copy is a Linked Table - from another Access database. Would this be the problem? Is there another way to get that table? Should I try importing that table?

In fact, I changed the source table to a local table within the database and the code ran w/out error! I suspect I'm missing something very simple here. So please let me know what it is!!

I appreciate any/all advice - I'm in the dark!!

- Lorentz

 

[tsosiel]

Okay. Here's what I've done with Importing.
I imported a table from the external database (not one that is linked to my database) - it worked fine!

I then removed the link to the table I want to import and tried the import; same error (Error 3197 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.) I think there is another link to some other source... I'm not sure!!!

In any case, I'm still in the dark!!! I've been at this for a week now and I'm nowhere to finding a solution. If anyone.... can shed some light on this - THANK YOU!!!

- Lorence

 

[mikevh]

I tried it on a linked table this time, with the code
exactly as I posted it last time, except for the name
of the table I'm selecting from. It ran fine.
Here's the code again.

Public Sub MakeMyTable()
On Error GoTo HandleError

Dim cnnMine As ADODB.Connection
Dim strCreateTable As String

Set cnnMine = CurrentProject.Connection
strCreateTable = "SELECT * INTO tblMyTable " _
& " FROM tblBadPhone1202;" ''this table name is only change from last post

cnnMine.Execute (strCreateTable)
cnnMine.Close

ExitHere:
Exit Sub

HandleError:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ExitHere

End Sub

If that still doesn't work for you, I'm afraid I'm out of
suggestions ... Perhaps someone more
knowledgeable than I can help.

 

[tsosiel]

Okay... I've isolated the problem (I think). This particular table has 4 fields:
Num (Number), ID (Number), TS (Date/Time), Desc (Memo)
At first I figured that NULL values in the TS field were causing the error; not so. The problem resides in the Desc field! After looking into this further, the table structure affects importing/action queries - it's not explicitly stated but inferred by me.

I'm looking into this again to figure out why I'm still getting the same error as before. No one else has the table open, the link is fine, any action-query/import on the other 3 fields are good. Something about this one memo field.

If there's any suggestion/comment, please let me know.

- Lorentz

 

[mikevh]

Is the DESC (Memo) field blank for any of the records in
the table you're trying to import?

 

[tsosiel]

All records in the table have a value for Desc.