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

 

[Andrzejek]

If your fields are: date as date and type as integer (bad names for the fields, BTW), I would do:

...
    Do While Not .EOF[green]
         'myrecordset.Sort = "date desc"[/green]
         strSQL = "INSERT INTO local_GemAcTable (date, type) VALUES (#" & !date.value & "#, " &  !type.Value & ");"
        Debug.Print strSQL
        .MoveNext
    Loop

    .Close
...

or forget all this 'record-by-record' and just do:
[tt]strSQL = "INSERT INTO local_GemAcTable (date, type) SELECT date, type FROM 1250"[/tt]

---- Andy

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

 

[combo]

Without VBA: you can create a link to external table and next create append query to add records from linked table to local table. If a part of data already exists in local table, delete query can be executed first.

combo

 

[Moss100]

This is what I have, but still no luck

Dim cnn As New ADODB.Connection
  Dim ACrecordset 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 ACrecordset = New ADODB.Recordset
  With ACrecordset
      
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
     
    strSQL = "SELECT * FROM 1250" & " ORDER BY Reference desc"
    .Open strSQL, cnn
    
    Set ACrecordset.ActiveConnection = Nothing
     
    If .RecordCount > 0 Then
     
     strSQL = "INSERT INTO local_GemAcTable (date, type) SELECT date, type FROM 1250"
      
    End If
     
  End With
   
  Set ACrecordset = Nothing
  cnn.Close
  Set cnn = Nothing

Also if possible could i select all the fields from the remote table and put them into the local one. Many thanks Mark

 

[Andrzejek]

How about my 'record-by-record' solution? Did you try that?

Have you tried Linking Tables in Access?

---- Andy

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

 

[Andrzejek]

You do know that you have [highlight #FCE94F]2[/highlight] ADODB Connections in your code:

Dim cnn As [highlight #FCE94F]New[/highlight] ADODB.Connection
...   
Set cnn = [highlight #FCE94F]New[/highlight] ADODB.Connection

You should either:

Dim cnn As New ADODB.Connection
...   
[s]Set cnn = New ADODB.Connection[/s]

or

Dim cnn As [s]New[/s] ADODB.Connection
...   
Set cnn = New ADODB.Connection

---- Andy

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

 

[Moss100]

Tried this below, but nothing in the local table. I thought you preferred not doing it record by record? Thank you for helping me.

Dim cnn As New ADODB.Connection
  Dim ACrecordset 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 ACrecordset = New ADODB.Recordset
  With ACrecordset
      
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
     
       
    strSQL = "SELECT * FROM 1250" & " ORDER BY Reference desc"
    .Open strSQL, cnn
    
    Set ACrecordset.ActiveConnection = Nothing
     
        
   If .RecordCount > 0 Then
     Do While Not .EOF
         'myrecordset.Sort = "date desc"
         strSQL = "INSERT INTO local_GemAcTable (date, type) VALUES (#" & !Date.value & "#, " & !Type.value & ");"
        Debug.Print strSQL
        .MoveNext
    Loop
    
    End If
     
  End With
   
  Set ACrecordset = Nothing
  cnn.Close
  Set cnn = Nothing
End Sub

 

[Andrzejek]

What do you have in the Immediate Window any time you execute [tt]Debug.Print strSQL[/tt] line of code?
Something like:
[tt]INSERT INTO local_GemAcTable (date, type) VALUES (#[red]1/1/2023[/red]#, [red]3[/red]);
INSERT INTO local_GemAcTable (date, type) VALUES (#[red]2/2/2023[/red]#, [red]5[/red]);
INSERT INTO local_GemAcTable (date, type) VALUES (#[red]3/3/2023[/red]#, [red]7[/red]);
INSERT INTO local_GemAcTable (date, type) VALUES (#[red]4/4/2023[/red]#, [red]2[/red]);[/tt]
where [red]RED data[/red] is coming from your recordset?

Actually, if I would have access to Gem.mdb (with linked tables) I would NOT copy any data. Why copy data if you already have it?

---- Andy

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

 

[Moss100]

The debug print shows records, but they are not in the table

 

[Moss100]

I need the data in the database so it can be viewed and manipulated, but does not impact the original data

 

[Moss100]

I have this - no red

INSERT INTO local_GemAcTable (date, type) VALUES (#25/04/2005 14:59:40#, Rent Paid);
INSERT INTO local_GemAcTable (date, type) VALUES (#25/04/2005 13:11:01#, Rent Due);
INSERT INTO local_GemAcTable (date, type) VALUES (#18/04/2005 14:22:01#, Rent Due);

 

[Andrzejek]

type is a text, so modify this, add single quotes:
[tt]strSQL = "INSERT INTO local_GemAcTable (date, type) VALUES (#" & !Date.value & "#, [highlight #FCE94F]'[/highlight]" & !Type.value & "[highlight #FCE94F]'[/highlight]);"[/tt]


In that case, replace the line:
[tt]Debug.Print strSQL[/tt]
with
[tt]DoCmd.RunSQL strSQL[/tt]

---- Andy

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

 

[Moss100]

The database which holds the data has thousands of tables so i can't link tables (not normalised, but a paid for application!)

 

[Moss100]

Boom!

so i also has to put DATE into brackets - as you said - poor name choice.

Is there a way to copy all fields from the source table to the desitination table, or must I reference each field (the structure of the source and destination tables are the same.

Anyway the below at least works

strSQL = "INSERT INTO local_GemAcTable ([date], [type]) VALUES (#" & !Date.value & "#, '" & !Type.value & "');"

 

[Andrzejek]

You can either use combo's suggestion (see his post), or you need to mention every field in your INSERT statement.

---- Andy

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

 

[Andrzejek]

If you have, let's say, 3 fields in both tables, you may skip listing the fields in your INSERT statement as long as you list ALL values in the same order as they are in the table, something like:

strSQL = "INSERT INTO local_GemAcTable VALUES (#" & !Date.value & "#, '" & !Type.value & "', " & !FieldX.Value & ");"

---- Andy

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

 

[Moss100]

Can i put the fields into a variable and use this? Thanks

i.e.

strfields = "[Date],[Type],[Amount in],[Amount out],[Adjustment],[No],[Tenant balance],[Current balance],[Deposit balance],[Repair balance],[Landlord balance]"

 

[Andrzejek]

The best way to know is to give it a try and see....

But yes, you can do that:

[blue]
strfields[/blue] = "[Date],[Type],[Amount in],[Amount out],[Adjustment],[No],[Tenant balance],[Current balance],[Deposit balance],[Repair balance],[Landlord balance]" 

strSQL = "INSERT INTO local_GemAcTable & " [blue]strfields[/blue] " & VALUES (#" & !Date.value & "#, '" & !Type.value & "', " & ![Amount in].Value & ", ...);"

---- Andy

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

 

[Andrzejek]

You know, you can get this list of fields from your recordset:

Dim ACrecordset As ADODB.Recordset
Dim strfields As String
Dim i As Integer

with ACrecordset
    For i = 1 To .Fields.Count
        If i = 1 Then
            strfields = "[" & .Fields(i).Name & "]"
        Else
            strfields = strfields & ", [" & .Fields(i).Name & "]"
        End If
    Next i
End With
MsgBox strfields

---- Andy

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

 

[dhookom]

I would use Combo’s no-code solution.

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