Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
Moss100

Moss100

Technical User
Aug 10, 2004
586
GB
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

Code: 
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
 
Sort by date Sort by votes
If your fields are: date as date and type as integer (bad names for the fields, BTW), I would do:

Code: 
...
    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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
This is what I have, but still no luck

Code: 
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
 
Upvote 0 Downvote
You do know that you have [highlight #FCE94F]2[/highlight] ADODB Connections in your code:
Code: 
Dim cnn As [highlight #FCE94F]New[/highlight] ADODB.Connection
...   
Set cnn = [highlight #FCE94F]New[/highlight] ADODB.Connection

You should either:
Code: 
Dim cnn As New ADODB.Connection
...   
[s]Set cnn = New ADODB.Connection[/s]
or
Code: 
Dim cnn As [s]New[/s] ADODB.Connection
...   
Set cnn = New ADODB.Connection

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Tried this below, but nothing in the local table. I thought you preferred not doing it record by record? Thank you for helping me.

Code: 
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
The debug print shows records, but they are not in the table
 
Upvote 0 Downvote
I need the data in the database so it can be viewed and manipulated, but does not impact the original data
 
Upvote 0 Downvote
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);
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
The database which holds the data has thousands of tables so i can't link tables (not normalised, but a paid for application!)
 
Upvote 0 Downvote
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 :)

Code: 
strSQL = "INSERT INTO local_GemAcTable ([date], [type]) VALUES (#" & !Date.value & "#, '" & !Type.value & "');"
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:

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

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Can i put the fields into a variable and use this? Thanks

i.e.
Code: 
strfields = "[Date],[Type],[Amount in],[Amount out],[Adjustment],[No],[Tenant balance],[Current balance],[Deposit balance],[Repair balance],[Landlord balance]"
 
Upvote 0 Downvote
The best way to know is to give it a try and see....

But yes, you can do that:
Code: 
[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
 
Upvote 0 Downvote
You know, you can get this list of fields from your recordset:

Code: 
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Add multiple contacts to outlook - from access table
Replies
12
Views
283
Moss100
Moss100
Moss100
  • Locked
  • Question
Vba adding record to table when field is integer
Replies
8
Views
318
Moss100
Moss100
btamsgn
  • Locked
  • Question
How to add lines from textbox to table 1
Replies
8
Views
182
MattGreer
MattGreer
Viswa.R
  • Locked
  • Question
Dimension import from Autocad to Excel
Replies
0
Views
272
Viswa.R
Viswa.R
BFroeschlKC
  • Question
Publisher Automation from Access Frustration
Replies
3
Views
497
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top