[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Changing ACCDE how can relink to the ACCDB file

Posted on 2011-04-26
6
Medium Priority
?
1,064 Views
Last Modified: 2012-05-11
We split our database into an ACCDE - code and forms and ACCDB - data files.

After making changes to the original db how do I relink the updated ACCDE file to the ACCDB.  The production ACCDB is not available to the development machine.

mlmcc
0
Comment
Question by:mlmcc
  • 3
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 35469725
You can incorporate the code below into your ACCDE - this is the de-facto standard:

http://access.mvps.org/access/tables/tbl0009.htm

And note the reference to the GetOpenFileName function ... to popup the Windows Standard File Browse dialog.

mx
0
 
LVL 101

Author Comment

by:mlmcc
ID: 35471790
I'll give it a try.

Thanks

mlmcc
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35474730
mlmcc

here is the code i use:
' if the back end is password protected you can pass it by changing
' strConnect = ";DATABASE=" & dbpath
' to strConnect = ";DATABASE=" & dbpath & ";PWD=" & "your back end password" '& ;"



Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim dbpath As String
Dim boolresult As String
Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Back End|*.accdb"
objDialog.FilterIndex = 1

boolresult = objDialog.ShowOpen
Me.txtlocation = objDialog.FileName
dbpath = "c:/mydatabase.accdb"   ' ///////////////////////////////////////path to database here

strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & dbpath


Set db = CurrentDb
For Each tdf In db.TableDefs

If tdf.Connect <> "" Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 10

Assisted Solution

by:conagraman
conagraman earned 500 total points
ID: 35474754
the above code uses a text box i got rid of the bad line


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim dbpath As String
Dim boolresult As String
Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Back End|*.accdb"
objDialog.FilterIndex = 1

boolresult = objDialog.ShowOpen
 
dbpath = objDialog.FileName   ' ///////////////////////////////////////path to database here

strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & dbpath


Set db = CurrentDb
For Each tdf In db.TableDefs

If tdf.Connect <> "" Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing
0
 
LVL 101

Author Comment

by:mlmcc
ID: 35712715
conagraman -  where do I put this code?
Do I add it to the ACCDE file and run it after installing on the client server?

The backend database will always be in the same folder with the ACCDE file.  Is there an easy way to tell it that?

mlmcc
0
 
LVL 101

Author Closing Comment

by:mlmcc
ID: 35751116
I haven't had a chance to try these since there have been no changes to the application.

The comments did remind of how to change the links manually and since they can all be done at one time I will probably do that with th enext changes.

Thanks

mlmcc
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question