Overwrting one Table with another Table using VBA?

[Guest]

Hello,

First off, I have 2 tables. "Table A" and "Table B". Both of these tables
have the exact data structure. What i would like to do, if possible, is take
Table A and its contents based on a certain criteria of which records to
include, and OVERWRITE Table B with that? (but keeping its name of "Table B"
also) Is this possible programmatically?

Thank you in advance!

MN

 

[John Spencer]

Do you want to completely replace all records in table b with the records in
table a?
Or do you want to add the records in table a to table B?

Easiest method is to use queries.

Replace All
DELETE * FROM [Table B]

INSERT INTO [Table B]
SELECT * FROM [Table A]
WHERE SomeField = "only these"

Assuming you are using dao

Dim strSQL as String
Dim dbAny as DAO.Database

Set dbany= CurrentDB()
StrSQL = "DELETE * FROM [Table B]"
DbAny.Execute strSQL

StrSQL = "INSERT INTO [Table B] " & _
" SELECT * FROM [Table A] " & _
" WHERE SomeField = 'only these' "
DbAny.Execute strSQL


Of course, I would add error checking to make this more robust.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thank you!!!

I want to do a mass replacement of the table so i'm using the second code
you provided. I put it into a module...and it highlighted the "set" and gave
me an error message saying "Compile error" that it is "invalid outside
procedure." Here is my code:

Set dbAny = CurrentDb()
strSQL = "DELETE * FROM [Input Form Table - Review]"
dbAny.Execute strSQL

strSQL = "INSERT INTO [Input Form Table - Review] " & _
" SELECT * FROM [Input Form Table] "




How do I fix this?

Thank you!!

Do you want to completely replace all records in table b with the records in
table a?
Or do you want to add the records in table a to table B?

Easiest method is to use queries.

Replace All
DELETE * FROM [Table B]

INSERT INTO [Table B]
SELECT * FROM [Table A]
WHERE SomeField = "only these"

Assuming you are using dao

Dim strSQL as String
Dim dbAny as DAO.Database

Set dbany= CurrentDB()
StrSQL = "DELETE * FROM [Table B]"
DbAny.Execute strSQL

StrSQL = "INSERT INTO [Table B] " & _
" SELECT * FROM [Table A] " & _
" WHERE SomeField = 'only these' "
DbAny.Execute strSQL


Of course, I would add error checking to make this more robust.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

First off, I have 2 tables. "Table A" and "Table B". Both of these
tables
have the exact data structure. What i would like to do, if possible, is
take
Table A and its contents based on a certain criteria of which records to
include, and OVERWRITE Table B with that? (but keeping its name of "Table
B"
also) Is this possible programmatically?

Thank you in advance!

MN

 

[John Spencer]

The code was only a snippet, it was not complete.

You need to declare a sub or a function and have the code in the function.
The entire thing might look something like:
"WARNING: UNTESTED CODE

Public Function fReplaceAll() as Boolean
Dim strSQL as String
Dim dbAny as DAO.Database

On error goto Whoops
Set dbany= CurrentDB()
StrSQL = "DELETE * FROM [Table B]"
DbAny.Execute strSQL, dbFailOnError

StrSQL = "INSERT INTO [Table B] " & _
" SELECT * FROM [Table A] " & _
" WHERE SomeField = 'only these' "
DbAny.Execute strSQL, dbFailOnError

fReplaceAll = True

Exit Function

Whoops:
msgbox Err.Number & ": " & Err.Description
fReplaceAll = False

End Function


Then you could use a button on a form to call the above function.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you!!!

I want to do a mass replacement of the table so i'm using the second code
you provided. I put it into a module...and it highlighted the "set" and
gave
me an error message saying "Compile error" that it is "invalid outside
procedure." Here is my code:

Set dbAny = CurrentDb()
strSQL = "DELETE * FROM [Input Form Table - Review]"
dbAny.Execute strSQL

strSQL = "INSERT INTO [Input Form Table - Review] " & _
" SELECT * FROM [Input Form Table] "




How do I fix this?

Thank you!!

Do you want to completely replace all records in table b with the records
in
table a?
Or do you want to add the records in table a to table B?

Easiest method is to use queries.

Replace All
DELETE * FROM [Table B]

INSERT INTO [Table B]
SELECT * FROM [Table A]
WHERE SomeField = "only these"

Assuming you are using dao

Dim strSQL as String
Dim dbAny as DAO.Database

Set dbany= CurrentDB()
StrSQL = "DELETE * FROM [Table B]"
DbAny.Execute strSQL

StrSQL = "INSERT INTO [Table B] " & _
" SELECT * FROM [Table A] " & _
" WHERE SomeField = 'only these' "
DbAny.Execute strSQL


Of course, I would add error checking to make this more robust.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

First off, I have 2 tables. "Table A" and "Table B". Both of these
tables
have the exact data structure. What i would like to do, if possible,
is
take
Table A and its contents based on a certain criteria of which records
to
include, and OVERWRITE Table B with that? (but keeping its name of
"Table
B"
also) Is this possible programmatically?

Thank you in advance!

MN