How to copy data from one table to another? 2

[TSimonick]

Hi,

I'm working on someone else's database and trying not to change the tables, but they are not normalized and I need to regularly import .csv files. I can use an append query to add the newly imported records into a temp table, but I need to copy the values from the the temp table into the the correct fields of the existing table based. If I don't then all the queries and forms will be referencing the wrong table. Can someone tell me how to write a VBA procedure to do that?

Thanks,

Tom

 

[gerard1979]

query the temptable. Make an INSERT INTO query and push the values on the right place
Syntax:
INSERT INTO <table>([field1],[field2],...) VALUES(..,..,..)
when it's not clear give the fields u have and the fields u need to import
Gerard

 

[TSimonick]

Gerard,

Isn't an Insert the same as an Append query? I thought that an append was adding rows to a table. My thinking must be too narrow, again. In the permanent table, tblMain, the fields are:

ID, Customer#, a,b,c,d,x,y,z,e,f,g,h

The temp table has Customer#, x,y,z

So my thinking was that if I use an append query I'm just adding hundreds of additional rows with lots of empty fields. Resulting in:

ID, Customer#, a,b,c,d,x,y,z,e,f,g,h
ID, Customer#, a,b,c,d,x,y,z,e,f,g,h
ID, Customer#, a,b,c,d,x,y,z,e,f,g,h
Customer#, x,y,z
Customer#, x,y,z

I don't understand how I'm actually inserting the data or is my terminology wrong?

I would appreciate more of your help with my lack of understanding.

Tom

 

[gerard1979]

My english is very bad so I don't understand exactly what u mean. I give it a try....

Some axioma's:
Main table is the table u need to fill?
TempTable is the table u make from other source?
Don't understand at all, but empty fields always be there while ur temptable has less fields then the permanent table.

maybe this will help: (set ur references in VBA to ado recordset 2.1 or 3.6)
dim rsTemp as adodb.recordset
dim strSQL as string
strSQL = &quot;SELECT * FROM TempTable

rsTemp.open StrSQL
with rsTemp
.movelast
.movefirst
while .EOF
strSQL = &quot;INSERT INTO tblMain([Customer#],[x],[y],[z]) VALUES (&quot; & .fields(&quot;Customer#&quot & &quot;,&quot; & .fields(&quot;x&quot & enz.
docmd.runsql strSQL
.movenext
wend
.close
end with
set rsTemp = nothing
this pushes the values from the tempTable into the tblMain.
it give many empty fields, but u cant change when u do nothing to the tblStructure,

hope this helps more, but u can come back when I don't understand well,
Succes
Gerard

 

[TSimonick]

Thanks Gerard!

Your English is good enough for me. Since I am new to VBA, it took me a while to understand your code was similar to an Update query and how it works. Now I need to learn some SQL.

Regards,

Tom

 

[IceCube]

Hi, maybe this might be a slolution:

It creates the target table if it doesn't excist and it askes the user to delete all data in case of excisting table.

try it and have fun with it.




'Function to copy data to another table.
'You pass the names of the source and the target to the function.
'N.B. If the target table doesn't exist it will be created.
Public Function CopyTable(strSourceTBL As String, strTargetTBL As String)

Dim dB As Database
Dim srcTbl As Recordset, tgtTbl As Recordset
Dim i As Integer
Dim tbl As TableDef
Dim myBool As Boolean
Dim fld As Field
Dim txtCrit As String

'On Error GoTo HandleErr

Set dB = CurrentDb
Set srcTbl = dB.OpenRecordset(strSourceTBL, , DB_READ_ONLY) 'Acces it READ ONLY to make sure you don't modify it.

'Check source for records
If srcTbl.RecordCount > 0 Then

srcTbl.MoveFirst 'move to first record if any records in table

Else

MsgBox &quot;The source table is empty. Nothing to copy.&quot;, vbCritical + vbOKOnly
Exit Function 'If no record tell the user and quit here

End If


'Check first to see if target table excists.
For Each tbl In dB.TableDefs

If tbl.Name = strTargetTBL Then
myBool = True 'If found set to true
End If

Next tbl

'Check to see if it needs to be created.
If myBool = True Then

Set tgtTbl = dB.OpenRecordset(strTargetTBL)

'Ask user if excisting data should be deleted.
If MsgBox(&quot;Do you want the existing data within the table &quot; & strTargetTBL & &quot; to be deleted?&quot;, vbInformation + vbYesNo) = vbYes Then
txtCrit = &quot;delete * from &quot; & strTargetTBL
dB.Execute txtCrit
MsgBox &quot;All data has been deleted from table &quot; & strTargetTBL, vbInformation + vbOKOnly
End If

Else

Set tbl = dB.CreateTableDef(strTargetTBL) 'Create tabledef

For i = 0 To srcTbl.Fields.Count - 1 'Add all fields from source

Set fld = tbl.CreateField(srcTbl.Fields(i).Name, srcTbl.Fields(i).Type, srcTbl.Fields(i).Size)
tbl.Fields.Append fld
tbl.Fields.Refresh

Next i

dB.TableDefs.Append tbl
dB.TableDefs.Refresh
Set tgtTbl = dB.OpenRecordset(strTargetTBL) 'Add table to tabledefs
'table has been added so the copying can begin.

End If

Do While Not srcTbl.EOF 'As long as there records, keep copying...

tgtTbl.AddNew 'add new record to target table

For i = 0 To srcTbl.Fields.Count - 1 'Count the fields

tgtTbl.Fields(i) = srcTbl.Fields(i)

Next i

tgtTbl.Update 'Update the record, otherwise it won't copy anything

srcTbl.MoveNext 'goto next record

Loop

'Tell user if finished.

MsgBox &quot;Copying done succesfully.&quot;, vbInformation + vbOKOnly

Set srcTbl = Nothing
Set tgtTbl = Nothing
Set dB = Nothing

'End of function

'Error handler
ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case Else
MsgBox &quot;Error &quot; & Err.Number & &quot;: &quot; & Err.Description, vbCritical, &quot;Module4.CopyTable&quot;
End Select

End Function

 

[TSimonick]

Wow,

Two good solutions in one thread! Thanks to both of you for taking the time to solve my problems. Seeing the complete code is really great for a novice. Gives me plenty to study, too.

Much appreciated!

Regards,

Tom