Transfer data from external table to identical local table - urgent please

[Moss100]

Hello,

I have a fairly urgenet need to work out how to connect to an extrenal access database and take all records from a named table and paste them into a table of identical structure in my local database.

I have tried transfering the table, but ran into problems with the imported table being renamed with copies buiding up suffixed with 1,2 3 etc.

I would like to import all the records and put them into my local table. This is what I have so far and it doent work. As a test Im setting the external table name which provides the data to 1250

Public Sub GemAcTablePopulate()

Dim cnn As New ADODB.Connection
Dim myrecordset As ADODB.Recordset
Dim CnnStr As String
Dim strSQL As String


CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
'CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=M:\gem\Gem.mdb"

Set cnn = New ADODB.Connection
cnn.ConnectionString = CnnStr
cnn.CursorLocation = adUseNone
cnn.Open

Set myrecordset = New ADODB.Recordset
With myrecordset
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly

Dim GemAccount As Variant
GemAccount = 1250
strSQL = "SELECT * FROM  " & GemAccount

.Open strSQL, cnn

If .RecordCount > 0 Then
myrecordset.Sort = "date desc"

strSQL = "INSERT INTO local_GemAcTable (date, type) VALUES (date, type);"

End If

.Close

End With

Set myrecordset = Nothing
cnn.Close
Set cnn = Nothing
End Sub

I would reall appreciate help with this - thanks Mark

 

[Moss100]

I can't really link the tables as there are thousands of them.

I came across the code below - is there any downside to using this?

CurrentDb.Execute "INSERT INTO GemAcTable SELECT * FROM 1250 IN 'C:\Gem\Gem.mdb'"
MsgBox "Done"

 

[dhookom]

Do you really need to import from thousands of tables?

I wouldn’t execute this statement without specifying the field names. I would also wrap a numeric table name in []s.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Hello Duane, I hope you are well.

Yes the database which I am linking to sets up a new table for each new account. There are around 4000 of them.

It’s an odd approach, but it is what it is.

I’ll tweak the code as you suggest. Is the method ok, it seems a very simple approach.

Do you know if using this method reads the source table as read only - im just wondering whether one method would be more reliable and less likely to cause a corruption in the source database.

Regards mark.

 

[combo]

Some time ago my external database changed (libraries, table names) so I had to update links. A sample from my code, requires:
- reference to ADOX library,
- in the below code link to table in Access database (for other database type other properties may be required?).
It allows to change linked table, so you can work with different tables using the same link. As I wrote above, a sequence of two queries executed after changing the link with DoCmd.OpenQuery will allow to update local table, alternatively it is possible to work witch updated link directly.

Sub ChangeLinkedTable()
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table, tblLinkNew As ADOX.Table
Dim strTblLinkName As String
Dim strTblLinkRemoteName As String

strTblLinkName = "tblLinked" ' linked table in local database
strTblLinkRemoteNewName = "NewTableName" ' ' remote table name for updated link
Set catDB = New ADOX.Catalog
Set catDB.ActiveConnection = CurrentProject.Connection
With catDB
    Set tblLink = .Tables(strTblLinkName)
    ' rename ...
    tblLink.Name = tblLink.Name & "_old"
    '  ...add new with previous name...
    Set tblLinkNew = New ADOX.Table
    With tblLinkNew
        .Name = strTblLinkName
        Set .ParentCatalog = catDB
        .Properties("Jet OLEDB:Link Datasource") = tblLink.Properties("Jet OLEDB:Link Datasource")
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = tblLink.Properties("Jet OLEDB:Link Provider String")
        .Properties("Jet OLEDB:Remote Table Name") = strTblLinkRemoteNewName
    End With
    ' append new table and delete old link
    .Tables.Append tblLinkNew
    .Tables.Delete tblLink.Name
End With
Set tblLink = Nothing
Set tblLinkNew = Nothing
Set catDB = Nothing
End Sub

combo

 

[Andrzejek]

the database [...] sets up a new table for each new account. There are around 4000 of them.

That's a really, really bad design, IMO. And you PAY for this?
I would ask for my money back.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

combo,
Your
[tt]strTblLinkRemoteName[/tt] is declared but never used, and
[tt]strTblLinkRemote[red]New[/red]Name[/tt] is used but never declared :-(

I would just [blue]pass the names[/blue] of the two tables, Sub easy to re-use:

Sub ChangeLinkedTable(ByRef [blue]strTblLinkName[/blue] As String, ByRef [blue]strTblLinkRemoteNewName[/blue] As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table, tblLinkNew As ADOX.Table
[s]Dim strTblLinkName As String
Dim strTblLinkRemoteName As String

strTblLinkName = "tblLinked"[/s] ' linked table in local database
[s]strTblLinkRemoteNewName = "NewTableName"[/s] ' ' remote table name for updated link
Set catDB = New ADOX.Catalog
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Thanks Andy, packing all in a function adds more functionality.
Originally it was a part of a bigger application in Excel, processing objects in a loop, as the renaming rules were clear, ADOX did the job flawlessly.

combo

 

[Andrzejek]

In thread701-1826088 there is a very interesting link to this site about 'some example sql to access data from another db without assigning it to a db object'.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson