Linked Tables Manually Changing.

cable · Mar 5, 2004

Is it possible to provide a way to change the linked tables via text entry?

Ie list the current link location and allow a user to select/cut/paste etc?

The reason I ask is I've got one db linked to two backends, and its a pain in the arse using the manual link system (because the dialog isn't big enough, and Access defaults to your 'home' dir rather than the current working dir).

Parker · Mar 5, 2004

I may be mis understanding the question but do you mean to pick between two different db's as a record source or do you mean something else?

cable · Mar 5, 2004

Parker said:
I may be mis understanding the question but do you mean to pick between two different db's as a record source or do you mean something else?

Yes kinda...the db links to multiple tables spread across two backends (in different dirs)

I've been working on the db in a 'dev' enviroment and it needs to be installed into the 'live' area...but relinking will be a utter pain in the butt using the linked table manager, (mainly because of the 8 levels of folders!!!)

But if I had a simple text box that can be altered then the job is reduced to a simple copy and paste exercise.

Parker · Mar 5, 2004

Hmm .... I had a nasty feeling you where going to say that.

I don't know of an easy way of doing this but someone else may.

However, Have you looked at other posible solutions?

Dose you "Dev" enviroment table structure match the live one. i.e. do you have the same table and folder structure in both?

Are you meaning to simply replace the tables in your "live" enviroment with the new tables in "Dev"? or is it more complex than this?

cable · Mar 5, 2004

Parker said:
However, Have you looked at other posible solutions?

Dose you "Dev" enviroment table structure match the live one. i.e. do you have the same table and folder structure in both?

Are you meaning to simply replace the tables in your "live" enviroment with the new tables in "Dev"? or is it more complex than this?

No, the structure is new

and there will be data manipulation before its linked.

I'm trying to think of other solutions...it would be nice if the planned move to SQL server happend earlier rather than later, then I could ditch the frankly poor design entirely.

Parker · Mar 5, 2004

Hmm every problem has a solution. Unfortunatly this one evades me at the moment maybe someone else can help that is more up to speed on this sort of stuff

I will think on it but I have to go out on call now so it will be a while.

cable · Mar 5, 2004

Parker said:
Hmm every problem has a solution. Unfortunatly this one evades me at the moment maybe someone else can help that is more up to speed on this sort of stuff

I will think on it but I have to go out on call now so it will be a while.

no probs, thanks for your time so far! I think I've got an idea...just to see if I can implement it now!

cable · Mar 5, 2004

Ok, got a solution. Create the following table:

Code:

CREATE TABLE LinkedTables (TableName TEXT, ConnectStr TEXT[255])

The use the following two subs:

Code:

Public Sub devGetLink()
'shoves all the linked tables into the LinkedTables table
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset

CurrentDb.Execute "DELETE * FROM LinkedTables;"
Set rs = CurrentDb.OpenRecordset("SELECT * FROM LinkedTables;")

For Each tdf In CurrentDb.TableDefs
    If Len(tdf.Connect) > 0 Then
        With rs
            .AddNew
            !TableName = tdf.Name
            !ConnectStr = Right(tdf.Connect, Len(tdf.Connect) - 10)
            .Update
        End With
    End If
Next tdf

DoCmd.OpenTable "LinkedTables", acViewNormal
End Sub

Which fills the table with the connection details. and

Code:

Public Sub devReadLink()
'refreshes links based on the LinkedTables table
Dim tdf As DAO.TableDefs
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM LinkedTables;")
Set tdf = CurrentDb.TableDefs
On Error Resume Next

Do Until rs.EOF
    SysCmd acSysCmdSetStatus, "Now linking " & rs!TableName & "..."
    
    tdf(rs!TableName).Connect = ";Database=" & rs!ConnectStr
    tdf(rs!TableName).RefreshLink
    
    rs.MoveNext
Loop

SysCmd acSysCmdClearStatus
End Sub

Which sets the connection string to the new value and refreshes the link.

Might need some tidying up...but seems to work!

Parker · Mar 6, 2004

I came up with a similar idea while looking at the back of an artic lorry on the M25

Create a table to store all the info and use a delete routine to get rid of an old link.
Then use the delete routine to fire a series of text boxes demanding info for the new link
Include an error routine that will fire if a table is unlinked

But by the time I got back and sat down to see if I could code it you had sussed it and came up with the code.

Saved the boredom of the M25 Friday evening car park though

Good luck

Rich · Mar 6, 2004

Parker said:
I came up with a similar idea while looking at the back of an artic lorry on the M25

Saved the boredom of the M25 Friday evening car park though

Good luck

Wouldn't it be quicker to walk?

Parker · Mar 6, 2004

Rich said:
Wouldn't it be quicker to walk?

Yep but don't tell my client's...... they pay us a lot of money to admire the traffic on the M25!

Linked Tables Manually Changing.

cable

Access For My Sins

Parker

Registered User.

cable

Access For My Sins

Parker

Registered User.

cable

Access For My Sins

Parker

Registered User.

cable

Access For My Sins

cable

Access For My Sins

Parker

Registered User.

Rich

Guest

Parker

Registered User.

Similar threads

Users who are viewing this thread