append

[rudeman76]

Hi,

I have 2 linked databases. I have information dumped from one of the databases and put into the other at the end of the week. The information in the first database is then deleted, so the next crew coming on has an empty database. I have this code for my tables, it works for the rest of them, but not this one.
my tables are linked properly and table names are correct. I would just like to know if my information is being just deleted or if is being put somewhere. I have looked in the actual database (i:\new\all.mdb ) and the info is not there. yet, this works for all the other tables.

            strAppend1 = "INSERT INTO [concentrate cans1] SELECT * FROM [concentrate cans]"
            strAppend1 = "Delete * from [concentrate cans]"
            DoCmd.RunSQL strAppend1

Andrew

[Dököll]

Hi,

I have 2 linked databases. I have information dumped from one of the databases and put into the other at the end of the week. The information in the first database is then deleted, so the next crew coming on has an empty database. I have this code for my tables, it works for the rest of them, but not this one.
my tables are linked properly and table names are correct. I would just like to know if my information is being just deleted or if is being put somewhere. I have looked in the actual database (i:\new\all.mdb ) and the info is not there. yet, this works for all the other tables.

[code=vb]
strAppend1 = "INSERT INTO [concentrate cans1] SELECT * FROM [concentrate cans]"
strAppend1 = "Delete * from [concentrate cans]"
DoCmd.RunSQL strAppend1
[/code]

Andrew

It sounds like you need this in VBA, and I could be way off base here. But if this is the case, please hang on, will fetch it and load and you can if can it be used...

[Dököll]

Here we go rudeman76:

http://www.thescripts.com/forum/thread743450.html

[CODE=VB]

Option Compare Database

Sub RunMultiSQL()

DoCmd.SetWarnin gs False

DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null And OpenDate Is Not Null And CloseDate Is Not Null ")

DoCmd.SetWarnin gs True

End Sub
[/CODE]

What I did was I set it to fire when Form_Loads, the query then runs. UPDATE can be DELETE in your case

(1) Active is a checkbox, you can turn that field to your textbox if you want
(2) ContactDate, OpenDate, and CloseDate are textboxes...

Come to think of it, it may be better handled as:

[CODE=VB]

Option Compare Database

Sub RunMultiSQL()

DoCmd.SetWarnin gs False

DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null")

DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE OpenDate Is Not Null")

DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE CloseDate Is Not Null ")

DoCmd.SetWarnin gs True

End Sub
[/CODE]

I believe this is what I have in my db now, thought to have read it works better. Please try it, and good luck with the project:-)

Dököll

[rudeman76]

hey,

I may not have been clear. My delete statement works fine, its the Insert part I am not sure about. I am trying to use insert to take the data from my "concentrat e cans" table and put it into "concentrat e cans1" table. The code works fine for 6 other tables, but for some reason it does not work with this one. It is supposed to take the info from the first table and put it into the second. I have this set up to activate with a log out button. The delete is working fine, cause the table is empty at the end of every week, but when I go back to look to see if the information is in the 'master' DB, I cannot find it.

I hope this is more clear and makes sense,

Andrew

[rudeman76]

I feel real stupid right now,

I was missing one line.
[code]
DoCmd.RunSQL strAppend1
[/code}

this should have been right after the first line.

thanks for all your help though, I really appreciate it,

Andrew