How to dynamically Link Tables through code 2

[HiBoo]

I've been assigned a project that involves the following steps...

Step 1 - Create recordset returning unique Store Numbers from SQL server database.
Step 2 - For each store number, create a new database and name it the same as the new database.
Step 3 - Create two local tables in the new database and populate it with specific data, based on the store number.
Step 4 - Create a linked table in the new database, pointing to a database located on a fixed drive.
Step 5 - Create a query in the new database that appends data from the newly created local tables(Step 3) to the linked tables(Step 4).


So far I've accomplished Steps 1, 2 and 3. Here is what I've done...

Option Explicit

Dim appAccess As Access.Application

Public Function NewAccessDatabase()

    Dim dbs As Object, tdf As Object, fld As Variant
    Dim strDB As String, strDBPath As String
    Dim rst As Recordset, qdf As QueryDef
    
    ' Loop through each of the Branches in the database
    Set qdf = CurrentDb.QueryDefs("qryUniqueBranches")
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    
    strDBPath = CurrentDBDir
    
    With rst
        Do While Not .EOF
            ' For Each of the Branches in the database, create a NEW ACCESS DB in it's own Name
            CreateAccessDatabase strDBPath, !COMPANY
            
            ExportTableDataHistHDR strDBPath, !COMPANY
            
            ExportTableDataHistLINE strDBPath, !COMPANY
            
            .MoveNext
        Loop
        .Close
    End With
    
    Set rst = Nothing
    Set qdf = Nothing
    
    MsgBox "Done!"
    
    
End Function

Public Sub ExportTableDataHistHDR(strDBPath As String, strCompany As String)

    Dim sql As String
    Dim qdf As QueryDef
    
    sql = "      SELECT dbo_HISTHDR.*"
    sql = sql & "FROM dbo_HISTHDR "
    sql = sql & "WHERE dbo_HISTHDR.COMPANY = '" & strCompany & "'"
    
    With CurrentDb
        Set qdf = .CreateQueryDef("qryHistHDR", sql)
        ' This exports the local table called tblLocal to the new db and calls it tblExported
        DoCmd.TransferDatabase acExport, "Microsoft Access", _
        strDBPath & "\" & strCompany & ".mdb", acTable, qdf.Name, "tblHistHDR"
        .QueryDefs.Delete qdf.Name
    End With
    Set qdf = Nothing
    sql = vbNullString
    

End Sub

Public Sub ExportTableDataHistLINE(strDBPath As String, strCompany As String)

    Dim sql As String
    Dim qdf As QueryDef
    
    sql = "      SELECT dbo_HISTLINE.*"
    sql = sql & "FROM dbo_HISTLINE "
    sql = sql & "WHERE dbo_HISTLINE.COMPANY = '" & strCompany & "'"
    
    With CurrentDb
        Set qdf = .CreateQueryDef("qryHistLINE", sql)
        ' This exports the local table called tblLocal to the new db and calls it tblExported
        DoCmd.TransferDatabase acExport, "Microsoft Access", _
        strDBPath & "\" & strCompany & ".mdb", acTable, qdf.Name, "tblHistLINE"
        .QueryDefs.Delete qdf.Name
    End With
    Set qdf = Nothing
    sql = vbNullString
    

End Sub

Public Sub CreateAccessDatabase(strDBPath As String, strCompany As String)

    Dim appAccess As New Access.Application
    Dim tdf As Object, fld As Variant
    Dim strDB As String
    
    ' Initialize string to database path.
    strDB = strDBPath & "\" & strCompany & ".mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application.9")
    ' Open database in Microsoft Access window.
    appAccess.NewCurrentDatabase strDB

    
    ' Close the database and empty it's object reference to clear memory
    appAccess.CloseCurrentDatabase
    Set appAccess = Nothing
    strDB = vbNullString
    
End Sub

What I'm lost on now is the creation of the Linked tables.(Step 4) I've got a database located on the Stores local drive C:\Operation\Data.mdb and I need to create the links within my code for each new database created in the process. The same would be true for the remaining steps as well. Any ideas or suggestions as to how I might best resolve this problem would greatly be appreciated.

Note: I'm using Access2K, I could use Access2002, and I'd really like to have the new databases Access97 complient so I may have to convert them through code as well. The purpose of all this it to push each new db to the appropriate store via ALTERIS and have the db update the local data on their machines.

Thanks for any help...

 

[TrojanRabbit]

DoCmd.TransferDatabase also works for linking tables, which I'm sure you know. To do it in the new database, you probably need to make the new database the current project. I know there are commands/objects for that, but I don't know the code. Access Help talks somewhere about making another db the current project. It may be in CurrentProject or in the Project method that it explains it. That might help you on your way.

 

[RexJacobus]

I have an Access 97 db that I use as a front end so that when the user starts it up it links to one of 8 sets of identically structured data which the user has chosen. Here is the code which the opening macro runs to link to the correct risk table.

DoCmd.DeleteObject acTable, "tblRisk"
DoCmd.TransferDatabase acLink, "Microsoft Access", sPath, _
acTable, "tblRisk", "tblRisk", vbNo

sPath being where the original data db is and the two tblRisks are what the table is called in the orignal data db and what you want it called in the linking db, respectively.

I hope this helps.

 

[oharab]

If you are wanting to make your db A97 compatible then you will need to make sure that you are using DAO to manipulate your objects, which you already seem to be doing.
Also, to improve performance and help your coding, you should define your objects when you dim them (this is called early binding) so your dims become

Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim strDB As String, strDBPath As String
Dim rst As DAO.Recordset, qdf As DAO.QueryDef


To create your linked table (warning-code written off top of head, watch for errors):

Sub CreateLinkedTable(sDatabaseName As String)
Dim db As DAO.Database, td As DAO.TableDef

Set db = OpenDatabase(sDatabaseName)
Set td = db.CreateTableDef("tblLinked&quot
With td
.Connect = ";DATABASE=C:\Operation\Data.mdb"
.SourceTableName = "tblData"
End With

db.TableDefs.Append td

Set td = Nothing
db.Close
Set db = Nothing

End Sub

You will need to change the connect string to match the location of the database you are linking TO and change the sourcetablename to match your requirements. The easiest way to get the connect string is to create a link to the database manually then type
?currentdb.tabledefs("linkedTableName&quot.connect
in the immediate/debug window. You can then just copy the string it returns into my code.

sDatabaseName is the full path and filename of the database you have created.
To add a query to an external db you use similar ideas:

Sub CreateQuery(sDatabaseName As String)
Dim db As DAO.Database, qd As DAO.QueryDef

Set db = OpenDatabase(sDatabaseName)
Set qd = db.CreateQueryDef("qryWhatever", "SELECT * FROM tblDATA;&quot
qd.Close
Set qd = Nothing
db.Close
Set db = Nothing
End Sub
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[HiBoo]

Thanks for the replies guys! I'll be working on this over the next little bit and update you as to my progress.

Thanks again...