Appending and Updating Tables with VBA

  • Thread starter Thread starter apatel85
  • Start date Start date
A

apatel85

Hello there,

I have about 100 tables which I want to append into 1 while updating
FileReference field. Is there a VBA scripts that can automate the
process?

So basically, I want to run instert into and update statement
together.

insert into table_A_Z
select *
from table_A

update table_A_Z
set FileRef = 'table_A'
where FileRef is null

I will really appreciate all your help and thanks alot in advance!!!!!


AP
 
Hey.. Forgot to mentioned that name of the tables varies.

Only similar thing is "MS2" for one table sets and "REB2" for another
table sets. I will be combining all MS2 table into one and REB2
tables into one.

Thank you for all your help!!!!!
 
Hello there,

I have about 100 tables which I want to append into 1 while updating
FileReference field. Is there a VBA scripts that can automate the
process?

So basically, I want to run instert into and update statement
together.

insert into table_A_Z
select *
from table_A

update table_A_Z
set FileRef = 'table_A'
where FileRef is null

I will really appreciate all your help and thanks alot in advance!!!!!


AP
Click to expand...

I'd suggest having a "driver" table with 100 records, one with the name of
each table. You can use the MSysObjects table to extract a list of existing
tablenames and then edit the result.

You could then use code like the following. It updates the FileRef field in
the process of loading the table so you don't need a separate update query.

Public Sub LoadUpAllTheTables()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error

Set db = CurrentDb
Set rs = db.OpenRecordset("DriverTable", dbOpenDynaset)
Do Until rs.EOF
strSQL = "INSERT INTO table_A_Z (field1, field2, field3, FileRef)" _
& " SELECT field1, field2, field3, '" & rs!Tablename & "' AS FileRef" _
& " FROM [" & rs!Tablename & "]";
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError ' run the query
rs.MoveNext ' go to the next table in the to-do list
Loop

Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Num & " in LoadUpAllTheTables:" & vbCrLf _
& Err.Description
Resume Proc_Exit
End Sub
 
Hello there,
Click to expand...
I have about 100tableswhich I want toappendinto 1 while updating
FileReference field.  Is there a VBA scripts that can automate the
process?
Click to expand...
So basically, I want to run instert into and update statement
together.
Click to expand...
insert into table_A_Z
select *
from table_A
Click to expand...
update table_A_Z
set FileRef = 'table_A'
where FileRef is null
Click to expand...
I will really appreciate all your help and thanks alot in advance!!!!!
Click to expand...
AP
Click to expand...

I'd suggest having a "driver" table with 100 records, one with the name of
each table. You can use the MSysObjects table to extract a list of existing
tablenames and then edit the result.

You could then use code like the following. It updates the FileRef field in
the process of loading the table so you don't need a separate update query.

Public Sub LoadUpAllTheTables()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error

Set db = CurrentDb
Set rs = db.OpenRecordset("DriverTable", dbOpenDynaset)
Do Until rs.EOF
   strSQL = "INSERT INTO table_A_Z (field1, field2, field3, FileRef)" _
   & " SELECT field1, field2, field3, '" & rs!Tablename & "' AS FileRef" _
   & " FROM [" & rs!Tablename & "]";
  Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
  qd.Execute dbFailOnError ' run the query
  rs.MoveNext ' go to the next table in the to-do list
Loop

Proc_Exit:
  Exit Sub
Proc_Err:
  MsgBox "Error " & Err.Num & " in LoadUpAllTheTables:" & vbCrLf  _
     & Err.Description
  Resume Proc_Exit
End Sub

--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you very much for all your help. I actually tried using this
and it seems to work.


Dim strSQL As String
Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef
Dim response


Set dbAny = CurrentDb()

response = MsgBox("Do you want to append all Market Share and
Rebate Tables?", vbYesNo)

If response = vbYes Then
MsgBox "Please wait..."

For Each tblAny In dbAny.TableDefs
If InStr(1, tblAny.Name, "MS2") <> 0 Then
strSQL = "INSERT INTO
[pbm_AZ_MarketShare_2007Q1_2007Q4]" & "SELECT * FROM [" & tblAny.Name
& "]"
dbAny.Execute strSQL
strSQL = "UPDATE
[pbm_AZ_MarketShare_2007Q1_2007Q4]" & "SET PwC_FileRef = '" &
tblAny.Name & "' WHERE PwC_FileRef is null"
dbAny.Execute strSQL
ElseIf InStr(1, tblAny.Name, "REB2") <> 0 Then
strSQL = "INSERT INTO
[pbm_AZ_Rebate_2007Q1_2007Q4]" & "SELECT * FROM [" & tblAny.Name & "]"
dbAny.Execute strSQL
strSQL = "UPDATE [pbm_AZ_Rebate_2007Q1_2007Q4]" &
"SET PwC_FileRef = '" & tblAny.Name & "' WHERE PwC_FileRef is null"
dbAny.Execute strSQL
End If

Next tblAny

MsgBox "You have joined Market Share and Rebate Tables and
Updated PwC_FileRef"

Else
MsgBox "Please try again"
End If
 
Thank you very much for all your help. I actually tried using this
and it seems to work.
Click to expand...

At a quick glance it looks ok... and the fact that it works is a good
indication! <g>

Glad to have been of help.
 
Back
Top