How do I import/rename tables between two different databases?

[Paladyr]

I want to open a connection to two different databases, then import tables from one into the other and also delete the old tables, then rename the ones I just imported. Is there a way to do this using Excel VBA? All I would need are the instructions for renaming/deleting, and importing tables into a database. Thanks for the help!

 

[Dreamboat]

Why are you trying to:

a) do this from Excel instead of Access?
b) why not just overwrite the old filenames instead and link them as tables into Access (which I assume you're using, but you didn't say)?
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Paladyr]

This has to be done programmatically. There should be no user input. Access and Excel both have VBA, I just chose excel because I felt like it was easier to do in there. I can't link them because of the potential repercussions that would have if the database were moved. If I can overwrite them, that would be fine.

 

[Paladyr]

I'll post in the VB forums

 

[Databaseguy]

Modify this to fit your needs. It explains how to link to an external table, name it, and delete it. As the code is written, it does absolutewy nothing...

Private Sub UpdateLinks()
'This imports all tables from a remote DB & deletes them
On Error GoTo Err_Hand
Dim dbsRemote As Database
Dim dbsLocal As Database
Dim tdfNew As TableDef
Dim tbl As TableDef
Dim i As Integer
Set dbsRemote = OpenDatabase("M:ACCESS\NewDB.MDB") 'Your destination table
Set dbsLocal = CurrentDb
For i = 0 To dbsRemote.TableDefs.Count - 1
Set tdfNew = dbsLocal.CreateTableDef(dbsRemote.TableDefs(i).Name)
With tdfNew
.Name = dbsRemote.TableDefs(i).Name 'Name the table
.Connect = ";DATABASE=m:\Access\NewDB.MDB"
.SourceTableName = dbsRemote.TableDefs(i).Name
dbsLocal.TableDefs.Append tdfNew 'Add the table
dbsLocal.TableDefs.Append tdfNew ' Delete the table
End With
Next i
Err_Hand:
If Err.Number = 3012 Then 'Table already exsists
Resume Next
Else
MsgBox Err.Number
End If
End Sub

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Dreamboat]

Wondered where you were, Tyrone! Yay! LOL
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[GeekGirlau]

A word of warning - in order to import a table and save it over an existing one, Access will have to delete any relationships between that table and any others on the database.

I'm not exactly sure of what you're trying to accomplish here, but have you considered importing the table, then running a series of queries to add new records, delete missing ones, and update any changes to data. You might also want to consider creating your database as a multi-user database with the tables linked to a server location.

 

[Paladyr]

Yes, all of your suggestions are good, and if I could, I would implement them right now. The problem is I am the sole IS person for a consulting group who have a bunch of databases they want me to improve upon lying around. They were creating by someone who knew nothing about database design and only knew how to use access. There are absolutely NO relationships setup, no indexes, no primary keys, the forms are lacking any kind of error proofing (allowing users to change the data on ANY form regardless of whether or not they should be able to) etc.... These databases also all use the same customer table, as well as three other tables. Every week I am supposed to pull the data from the "master" database and import in into all the other databases floating around. The whole data collection process here is HORRIBLE. Queries that are the data source for some of the forms return eight duplicates of each record because the database has not been normalized. They have duplicate fields in each record so that each customer can have multiple rooms (Room1, Room2, Room3, all included in every record instead of being its own table). So as you can see, right now I am doing what I can to make things as simple as possible for me. There is so much that would have to change if I were to change the layout/relationships of the database that I really need to sit down for about a month and figure out how the heck I am going to do it.

Hope you all got this far after reading that . I do have one question about this:

dbsLocal.TableDefs.Append tdfNew 'Add the table
dbsLocal.TableDefs.Append tdfNew ' Delete the table

Did you mean to put append twice or is there a different command for deleting it? Thank you for the help!!!

 

[Paladyr]

Also, when you assign dbsLocal = CurrentDb, what exactly happens there? I know I have seen that before and I USED to know what that did, but I need a reminder . Thanks again!

 

[Dreamboat]

Wow! Have you got your work cut out for you!
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Databaseguy]

>>The problem is I am the sole IS person for a consulting group who have a bunch of databases they want me to improve upon lying around. They were creating by someone who knew nothing about database design and only knew how to use access<<

Yeah, I had one here that had 75 columns. Variable1, Variable2.......Variable75. Took me a week just to normalize it.....


Good luck to you !!! Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Paladyr]

haha, thanks for all the sympathy . I'm reposting a new question so check that one out!