Can you "set" an answer to a warning message? 1

[CarlTipp]

I am trying to replace a table in another database with a table in the current database using the docmd.copyobject comand.

The coding works, however, I always get the warning "The name you entered already exists ... Do you want to replace the existing table?"

SetWarnings doesn't work because the default is "No" and I need "Yes".

Is there a way to set the SetWarnings to answer something other then the default?
If not, is there another way to have automatically answer the warning with a "Yes".

Thanks!!

 

[LittleSmudge]

As usual there is a way - but it is a little off centre from your original track.

SetWarnings OFF
Delete the offending table in the other database
Copy the table you want to the new location ( now there is no name conflict )
SetWarnings ON


G LS

 

[CarlTipp]

The problem with that is that the tables are linked.
If I could copy I wouldn't have a linked file conflict.

If I cant't do it with a copyobject, then how about...
Is there a way I could select all the data in tableA, copy it, open tableB, delete * form tableB and paste tableA records in tableB?

 

[thevillageinn]

CarlTipp,
I would do what you said in your last post.

I would delete all the records in table B and then append the data from table A into it.

I don't remember all the SQL right off the top of my head, but I can try to get you started.

Delete * from tableB

Insert into tableB from tableA

Hope that helps you a bit.
-Dan

 

[qureshi]

Delete contents of tableA and then append to this table the data from tableB.
or try the following
drop table A and the create table B

 

[KyleS]

The exact SQL you're looking for is this...

DELETE *
FROM TableA;

and then

INSERT INTO 1
SELECT *
FROM TableB;

You could just put this in a module like this

Sub DoYourThing()
Docmd.SetWarnings OFF
Docmd.RunSQL "DELETE * FROM TableA;"
DoCmd.RunSQL "INSERT INTO 1 SELECT * FROM TableB;"
Docmd.SetWarnings ON
End Sub Kyle :

 

[thevillageinn]

yes, that is what I was thinking of...thanks Kyle for bolstering my post with useble code!

-Dan

 

[KyleS]

Well, one corection, allow me to replace my code:
Sub DoYourThing()
Docmd.SetWarnings OFF
Docmd.RunSQL "DELETE * FROM TableA;"
DoCmd.RunSQL "INSERT INTO TableA SELECT * FROM TableB;"
Docmd.SetWarnings ON
End Sub

I left the name of a junk table "1" in the SQL statement, so NOW it's useable code.

Dan- no problem all I did was expound upon you post! Kyle :

 

[CarlTipp]

Thanks everyone, the "Insert Into" worked great!!
I'm kinda new at this stuff and it's nice to know you can get good info from experienced people!!

 

[thevillageinn]

Carl,
You and me both (are new to this stuff)...This forum and it's members are very helpful.

I am excited by all the possibilities when I work with Access now (something I do most all day every day). I just began delving into VBA fairly recently.
-Dan