Linking to a new table every month.

[chanman525]

I am working on speeding up a database that is running really slow. The problem is stemming from the database importing picking transaction histories into a table everytime you hit the update table button. This information is already out there in a table in another database. I want to link to that table, but the problem is, the tables changes with the months.

The database that I want to link to is G:\Transaction Histories\picking.mdb and the tables are dated as tbl2006-05

Does anyone have any information on how to link to these every month?

 

[hneal98]

The only way you can do that is if some how the tables stay consistent. Is there any way to automate taking the data from the other data base and putting it in a table that this database is linking directly to?

 

[chanman525]

The tables do stay consistent as in, they are always
tbl(year)-(month).

I guess that on opening the database I could import the current and previous month. But that would make it really slow at the beginning.

 

[hneal98]

right, but the year and month change. You need the whole name to stay the same. Is the other database you mentioned a backend database, or is it another application?

 

[Remou]

Perhaps something like:

Sub LinkEachMonth()
strTableName = "tbl" & Format(Date, "yyyy-mm")
strDBName = "G:\Transaction Histories\picking.mdb"
strNewName = "tblStandard"
If TableExists(strNewName) Then
    DoCmd.DeleteObject acTable, strNewName
End If

DoCmd.TransferDatabase acLink, "Microsoft Access", strDBName, acTable, strTableName, strNewName
End Sub

Function TableExists(strTableName)
'Credits to PHV
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
On Error Resume Next
Set td = db.TableDefs(strTableName)
If Err.Number <> 0 Then
  TableExists = False
  Err.Clear
Else
  TableExists = True
End If

End Function

 

[JerryKlmns]

So if you link the MSysObjects table of G:\Transaction Histories\picking.mdb

and use this query

SELECT Top 1 MSysObjects1.Name
FROM MSysObjects1
WHERE MSysObjects1.Name Like "tbl????-??"
ORDER BY MSysObjects1.Name DESC;

you 'll have the latest table name!

Then you could re-link the table X from tbl2006-04 to the the new one tbl2006-05 returned from the previous query