linking tables externally 4

[bistec]

I'm trying to use an seperate mdb file (like an update to run at the remote site), to create a new table in the backend db to the frontend db.

I've had a look at "TransferDatabase" but that seems to only be configurable to run in the frontend db only.

Creating the new backend table is not a problem, it's creating the new link to the frontend that is.

Does someone know of a way to use vba to link the new table to the frontend db using an external db.

TIA
Howard

 

[fdalmoro]

Buddy, I don't know what you mean exactly but I know how to link a regular frontend to a backend. All you have to do is go to the tables screen on the frontend. RightClick on a blank space and click Link Tables... Then select the backend database and follow the directions.

Hope this helps

 

[BillPower]

Hi Howard,

This will Update the BackEnd DB from your Update DB:

Dim strDB As String
On Error GoTo Err_Export
strDB = "c:\My Documents\BE.mdb"
DoCmd.TransferDatabase acExport, "Microsoft Access", _
strDB, acTable, "Demo", "Demo"
Exit Sub
Err_Export:
MsgBox "Error No. " & Err.Number & " - " & Err.Description

This will Link your FrontEnd DB(s) to the New Table in your BackEnd DB from your Update DB:

Dim ws As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
On Error GoTo Err_LinkTable

Set ws = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = ws.OpenDatabase("FE.MDB&quot 'assume FE.mdb is in the Current Directory

Set tdf= dbs.CreateTableDef("Demo&quot
tdf.Connect = ";DATABASE=C:\My Documents\BE.mdb"
tdf.SourceTableName = "Demo"
dbs.TableDefs.Append tdf
dbs.Close
Exit Sub
Err_LinkTable:
MsgBox "Error No. " & Err.Number & " - " & Err.Description

Change anything in red to meet your own requirements. You will need Microsoft DAO 3.x Object Library installed in your References.

Let me know if you have any problems with this.

Regards

Bill

PS. I'm going to do a Multi-Table version of this for myself over the weekend. Let me know if you're interested in me posting the code here.

 

[janeatwork]

I for one would very much like to see you post the code BillPower. Thank you for this. I can now upgrade client databases by just emailing an update.mdb

Thanks again,

Jane
jmooreathome@tiscali.co.uk

 

[hpicken]

Thanks Bill

That should do the job. (I have two acounts here one for work and one for home and I haven't had time to look at your response from work, hence the delay).

Thanks very much and yes I would be interested in see the code for multiple BE dbs.

If I could work out how to give you a couple of stars I would.

Howard

 

[BillPower]

Sorry for the delay in getting around to posting this, it wasn't as easy as I thought to accomplish.

For this to work you will need to create a New Table in the Update.mdb.

Table Name:
tblUpdate

Field Names/Data Type
ObjectName/Text
ObjectType/Number
Link/Yes/No

This will Transfer any amount of Tables, Queries, Forms etc from the Update Database to your Front End Database:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strDB As String, strObject As String, intObjectType As Integer
On Error GoTo Err_Export
strDB = Application.CurrentProject.Path & "\BE.mdb"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblUpdate&quot
With rst
Do While Not .EOF
strObject = .Fields("ObjectName&quot.Value
intObjectType = .Fields("ObjectType&quot.Value
DoCmd.TransferDatabase acExport, "Microsoft Access", _
strDB, intObjectType, strObject, strObject
.MoveNext
Loop
End With
Exit Sub
Err_Export:
MsgBox "Error No. " & Err.Number & " - " & Err.Description

This will Link any amount of Tables from the Back End Database to the Front End Database:

Dim rst As DAO.Recordset, ws As DAO.Workspace, dbs As DAO.Database, tdf As DAO.TableDef
Dim Cdbs As DAO.Database, strPath As String
Dim strDB As String, strObject As String, intObjectType As Integer, booLink As Boolean
On Error GoTo Err_LinkTable
strPath = Application.CurrentProject.Path
strDB = "\BE.mdb"
Set ws = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = ws.OpenDatabase(strPath & "\FE.MDB&quot
Set Cdbs = CurrentDb
Set rst = Cdbs.OpenRecordset("tblUpdate&quot
With rst
Do While Not .EOF
strObject = .Fields("ObjectName&quot.Value
intObjectType = .Fields("ObjectType&quot.Value
booLink = .Fields("Link&quot.Value
If intObjectType = 0 And booLink = True Then
Set tdf = dbs.CreateTableDef(strObject)
tdf.Connect = ";DATABASE=" & strPath & strDB
tdf.SourceTableName = strObject
dbs.TableDefs.Append tdf
End If
.MoveNext
Loop
End With
dbs.Close
Exit Sub
Err_LinkTable:
MsgBox "Error No. " & Err.Number & " - " & Err.Description


You can download the Code from

http://www.wpower.fsbusiness.co.uk/

the file to download is Update.zip. Place the enclosed DB's (Update.mdb, FE.mdb, BE.mdb) in a directory, Open Update.mdb, click on the Transfer Database Button then Link External Button to see how it works.

Hope it works for you.

Bill

 

[bistec]

Thanks for that Bill, it looks good. I'm a bit tired up at the moment (aren't we all) so I'll have a look at it later this week.

cheers
Howard

 

[Gumlunorth]

Great stuff Billpower - just what I was looking for.