How to Append Records from One Access Table to Another Access Table 1

[Hiccup]

My setup is VB6/Access2K. I need a click event on my VB Form that will append all of the Records from one Access Table (tblTEMP) to another Access Table (tblPERM)in the same Access mdb.

Can someone help me with this one?

Thanks in advance!

 

[DrJavaJoe]

Insert into tblPERM ( field1, field2, field3, etc. )
Select field1, field2, field3, etc.
From tblTemp



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Hiccup]

Once again DrJavaJoe, Thanks! And another star!

 

[Hiccup]

DrJavaJoe....it didn't like a couple of things. I got this Compile error on the tblPERM:

Expected: end of statement

And this Compile error on the first field:

Expected: Case

Here's the code I entered:

Private Sub Command1_Click()
Insert into tblPERM (DepDate, DepNo, Tenant, etc.)
Select DepDate, DepNo, Tenant, etc.
From tblTEMP
End Sub

Any ideas?

 

[DrJavaJoe]

That was just an example of the SLQ statement you'll need to make a reference to Microsoft ActiveX Data Objects and do something like the following. Also unless you have a field called etc you'll have to drop that and list all the fields. Now this is not going to drop any records from the temp table so everytime you click command1 it is going to insert these records again into tblPerm, so you might want to run a delete query after the insert.

Private Sub Command1_Click()
Dim adoCmd As new ADODB.command
adoCmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourPathToTheAccessFile;User Id=YourUserName;Password=YourPassword;"
adoCmd.Commandtext = "Insert into tblPERM (DepDate, DepNo, Tenant, etc.) " & _
"Select DepDate, DepNo, Tenant, etc. " & _
"From tblTEMP "
adoCmd.execute
End Sub



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Hiccup]

Thanks DrJavaJoe, I'll give it a try!

 

[Hiccup]

DrJavaJoe, I'm having trouble with the delete records.

I tried this (with no luck) to delete the records from the tblTEMP:

Private Sub Command1_Click()
Dim adoCmd As New ADODB.Command
adoCmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EHicks\RESIDENTIAL PROPERTY MANAGER\RPM.mdb;Persist Security Info=False"
adoCmd.CommandText = "Insert into tblPERM (DepDate, DepNo, Tenant, TenantID, UnitNo, RentRate, SecDep, PetDep, PrevBalDue, VacantAmnt, LateChrg, PymtRcvd, NewBalDue, CkAmnt, TotalPayments, TotalPymtsMinusNSFCks) " & _
"Select DepDate, DepNo, Tenant, TenantID, UnitNo, RentRate, SecDep, PetDep, PrevBalDue, VacantAmnt, LateChrg, PymtRcvd, NewBalDue, CkAmnt, TotalPayments, TotalPymtsMinusNSFCks " & _
"From tblTEMP"
Delete "Select DepDate, DepNo, Tenant, TenantID, UnitNo, RentRate, SecDep, PetDep, PrevBalDue, VacantAmnt, LateChrg, PymtRcvd, NewBalDue, CkAmnt, TotalPayments, TotalPymtsMinusNSFCks " & _
"From tblTEMP"
adoCmd.Execute
End Sub

I got this compile error "Sub or function not defined" and the "Delete" was highlighted.

Could you help me with the DELETE query part of the code; guess I'm not sure exactly how to set it up?

Thanks!

 

[DrJavaJoe]

I hope this is a test DB.

adoCmd.CommandText = "Delete From tblTEMP"
adoCmd.Execute



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Hiccup]

Thanks DrJavaJoe, that worked. That completes my app and everything is working fine thanks to you. And another star for that one!