Overwrting one Table with another Table using VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MacNut2004 - 27 Sep 2007 19:33 GMT

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

Reply to this Message

John Spencer - 27 Sep 2007 20:10 GMT

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.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hello,
>
[quoted text clipped - 10 lines]
>
> MN

Reply to this Message

MacNut2004 - 28 Sep 2007 14:35 GMT

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?
[quoted text clipped - 38 lines]
> >
> > MN

Reply to this Message

John Spencer - 28 Sep 2007 14:57 GMT

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.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Thank you!!!
>
[quoted text clipped - 61 lines]
>> >
>> > MN

Reply to this Message