Rename and Merge Tables in acces

[ootkhopdi]

Hi all

i have a database with 150 table with different names

1. I want to rename all tables as 001,002,.....
2. iwant to merge all tables into new table name "Merged".


please give me solution...


thanks in advance

 

[Minty]

Can we assume all the tables have the same layout?

If they do, create a new database and link just the tables you want to merge into the new database. You can then in VBA get a list all the tables, and loop around them inserting the contents into your merge table. I would add a field to the merge table that stores the original table name, just for debugging if it goes wrong.

No need to rename them.

 

[ootkhopdi]

Yes. All tables have same structure but different records..
so i want to merge all records of all tables into one table...

 

[Minty]

So to get a list of the linked tables in your new database you could use something like

for each tdf in currentdb.tabledefs
     if tdf.connect<>"" then
           msgbox("linked table: " & tdf.name)
     end if
next

 

[ootkhopdi]

Thanks..
but i have very little knowledge in access..
so please give me complete steps or solution

 

[Ranman256]

you must rename rename them manually. vb code wont know what table to name 001, etc.
instead, create a table of all table names, remove the tables not needed
then run this code that loops thru the list, and append the data to the master table.

   'make a list of all tables
Public Sub ListAllTbls()
Dim tdf As TableDef
Const kTBL = "tTables"

DoCmd.SetWarnings False
sSql = "SELECT 'start' AS TableName INTO tTables"
DoCmd.RunSQL sSql

sSql = "delete * from tTables"
DoCmd.RunSQL sSql

For Each tdf In CurrentDb.TableDefs
    If InStr(tdf.Name, "~") > 0 Or InStr(tdf.Name, "msys") > 0 Then
        'dont use system nor delete files
    Else
        sSql = "INSERT INTO " & kTBL & " ([TableName]) values ('" & tdf.Name & "')"
        DoCmd.RunSQL sSql
    End If
Next
DoCmd.OpenTable kTBL

DoCmd.SetWarnings True
Set tdf = Nothing
End Sub

  'scan the table list and append their data to the master table
Public Sub ApdTblsInList()
Dim rst
Dim vTbl
Dim sSql As String

Set rst = CurrentDb.OpenRecordset("select * from tTables")
With rst
   While Not .EOF
      vTbl = .Fields(0).Value & ""
      
      sSql = "insert into tMasterData SELECT * FROM " & vTbl
      DoCmd.RunSQL sSql
   Wend
End With
Set rst = Nothing
End Sub

 

[ootkhopdi]

thanks ..ranman

but i want to merge all records into one table..not list of all tables

 

[Minty]

Please read the code and comments Ranman provided - it does insert the data.

 

[ootkhopdi]

sorry ..
but when i run second part of code it shows error, Run Time Error '3049'
Cannot Open Database...

What is the problem...

please

 

[Minty]

How big is the database ? That error can be caused if the DB file grows too big. https://www.access-programmers.co.uk/forums/showthread.php?t=207114

 

[ootkhopdi]

yes it was too big...app 2gb

i compact it but their a little problem too..

in these table many records are same or duplicate..

so if any code for stop appending duplicate record,, please give me..


thanks

 

[Minty]

You need to make an effort to identify and solve some of these issues yourself.
What fields indicate that it is a duplicate entry?
Use those restrict your import to new records only.

Try to create some queries that select the records you need first then try and incorporate that into the code already provided. You won't learn anything otherwise.