Link table to new database source 1

[kastaman]

Hi there,

I'm creating a database that will link to a table. The problem I have is that every month, they recreate the DB and change the name of the DB to reflect the month/yr (DBName_09/2004).

Is there a way to update my table links for any month as a pop-up screen or at any point I have the DB open?



Thanks in advance,

Kastaman

 

[randy700]

You could include code that removes the link to the old database and creates a link to the new one. Something like this...

DoCmd.DeleteObject acTable, TableName
Set db = DBEngine.Workspaces(0).OpenDatabase("NewDbName")
DoCmd.TransferDatabase acLink, "Microsoft Access", "NewDbName", acTable, "SourceTableName, "LinkedTableName"

Randy

 

[kastaman]

How does the code get triggered?
I was hoping for a way for me to dictate which table I'd like to connect to by way of filling in the source table info.

Thanks in advance,

Kastaman

 

[randy700]

Use text boxes or input boxes to fill in the source table and any other information that needs to be entered manually. Place the code in the click event of a command button. You might have to modify the code to reflect the text box or variable names, such as....

DoCmd.TransferDatabase acLink, "Microsoft Access", strNewTableName, acTable, strSourceTableName, strLinkedTablename

or

DoCmd.TransferDatabase acLink, "Microsoft Access", me.txtNewTable, acTable, me.txtSourceTableName, me.txtLinkedTableName

Randy

 

[kastaman]

Thanks Randy,

I created a Macro to import the data, but there are 2 files that need to be imported each month.

Would it be easier just to run the import command 2x, each time identifying which month and file to be imported rather than creating 2 commands that will ask which files to import from the text boxes?


Thanks in advance,

Kastaman