HOW TO MOVE RECORDS FROM ONE ACCESS TABLE TO ANOTHER FROM VB6

[Hiccup]

My setup is VB6/Access2K. In VB, I have a DataGrid connected via an Adodc to a table in Access named TEMPTable. In Access, I have the TEMPTable and a table named PERMTable. The TEMPTable is for processing rent checks for the current deposit. When it's completed I need the VB6 cmdButton, when clicked, to append the Records from the TEMPTable to the PERMTable then empty the TEMPTable of all Records so that it can be used again for a new rent deposit.

I've tried several approaches, but can't get it to work.

Appreciate any suggestions! Thanks in advance.

 

[fredericofonseca]

use ADO with plain SQL. DON'T use the adodc.


sql1 = "insert into PERMTable select * from TEMPTable"
sql2 = "delete from TEMPTable"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Hiccup]

fredericofonseca - Unfortunately, my entire setup is based on a DataGrid connected to the Access2K mdb Tables via an Adodc connection.

Will your SQL approch still work if I leave my setup as is?

 

[fredericofonseca]

You can have both ADO and ADODC on the same form.

But for what you are trying to do I don't even know if adodc will work.

But the way I said will definitely work
e.g. either do it as an individual connection

dim dbconn as adodb.connection

dbconn.open sql1 blabla

or use a command object. (my preferred).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Hiccup]

fredericofonseca - By Command Object, do you mean the Command Button I have on my Form to transfer the Records from the TEMPTable to the PERMTable?

If so, this is the code I figure I should use:

Private Sub Command1_Click(Index As Integer)
sql1 = "insert into PERMTable select * from TEMPTable"
sql2 = "delete from TEMPTable"
End Sub

Does this look like it will work?