I currently manage 6 databases each have the same data but for diferent states. I publish a new front end with the same script except the linked tables are diferent. For example. Bob works in NYC, Sam works in Texas. I currently publish a release for NYC with linked tables to NYC.mdb (backend) and one (Texas.mdb) front end. The link tables such as Ordertype and Provisioner List. These tables have the same fields but diferent backend coppies have diferent data. Can anyone help?

Yes
Ive gone through this and developed auto linking

see the UA thread "Updating table links from another database"

you can re-link directly from the front to the back without using a third database - the easiest situation is if you can always keep the back in the same directory as the front - the next is if the back is in a different dir from the front but always in the same place - the most complicated is when we have to get the user to point to the back because its not consistently in the same place - but all are possible

regards
Paul Churchill

Sorry I ment to end my post with a question:-
Which scenario of the 3 do you have?

regards
Paul

Currently i have a front end installed on each users machine. All tables are linked to a backend. These are on the same shared drive just diferent folders. The backend has the same name the only thing seperating them is the folder it is located in for example. NYC is in Reject Process\NYC\Database\Reject Data.mde and the MDE the users have is installed on the pc thay are working on. The user doesnt know what the database backend is or where it is located. They have verry limited access to forms only. But i would like to be able to make one change and on startup the database would be able to redirect to the correct backend. I use a global verable openversion= "NYC" to assign what version or state they are working in. so the script should read something like "n:\reject process\" & openversion & "\Database\Reject Data.MDB" but i am not sure of the command to change the linked tables to that file.

Ah . . . you may find this post of interest . . .

Swap/Relink Between LIVE and TEST Data

It is highly configurable and will work in any Access verrsion A97 and above.

hope this helps

I posted the re-linking code in the UA thread "Updating table links from another database"
Paul

I appreciate the info. The script works however It will not allow me to delete the old linked table because it is part of a relationship. Is there anyway to just change the linked tables insted of deleting and recreating?

If you have the relationship set up in the front (and the tables in the back) then yes I would expect this problem, the relationship needs to be in the back where the tables are.

Regards
Paul

I will try and make that change. Thanks for all your help.

Hello there,

i'm new in using access. I just dl the relinker and was wondering in how do i use it towards the application i have. Do i need to copy all the forms and paste it into the new application? Any help will be greatly appreciated. Thank you.

phong919,

You posted your request in an active thread. Please repost to a new thread in the appropriate forum.

Thanks!

I have another issue. I am using that script but my backend has a password. How do i make it relink to the database and enter the password without the user being prompted?

You don't use a password - we lock and password it up by other means see the attached sampleDB.mbd

the pass word is "commonback"

Actually i found a script from microsoft that i adjusted to do what i needed.
http://support.microsoft.com/kb/235422

But thanks for all your help.

My first solution was to use the Transferdatabase function. This worked well to connect to the database without much trouble. The only issue is when you try and use a database (BE) password. By the way i am using access front end and backend. I was using verables that would adjust what BE they would connect to based on what market thay are assigned to. example:

DoCmd.TransferDatabase acLink, "Microsoft Access", "\\Kcnpfp03\data1\Com Reject Process\" & openversion & "\Database\Reject Data.mdb", acTable, "Native/Port", "Native/Port"

now the openversion is a verable that is pulled form a users table that i created. i then created folders with that as the name so they cordinate exactly. Now when i tried to connect to one that had a password this method didnt work. So i was looking and found:

Option Compare Database
Option Explicit

Sub OpenPasswordProtectedDB()

'Define as Static so the instance of Access
'doesn't close when the procedure ends.
Static acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
strDbName = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=nwind")
acc.OpenCurrentDatabase strDbName
db.Close
Set db = Nothing
End Sub
i then replaced the table names with veriables that will adjust to the correct table i am trying to link then just repeat this script alternating it with a new verable set that would allow me to connect to one at a time but end up with all attached and still have the DB password inplace. I would like to find a more efficent solution but this works verry well considering. Thanks to all you who posted suggestions. I really appreciate all the help.

<<My first solution was to use the Transferdatabase function. This worked well to connect to the database without much trouble. The only issue is when you try and use a database (BE) password.>>

That's why I secure the back without using the Access password (which isn't that secure anyway - I have a password cracker for the whole office suite) and once the front is open and connected then a user can delete from there

What other option is there to secure the BE? I did put a startup script on that that checks user level before allowing access however anyone can still open the BE directly by just doing linked tables. And they have full unpeded access to the data. I know atleast with the PW in place it cannot be gotten into that easly. But i would like any info you have on making the backend more secure anyway possible. This database is a huge money maker for my company and I would like to secure it anyway possible.

<<anyone can still open the BE directly by just doing linked tables. And they have full unimpeded access to the data>>

Well they have that through the front and If they know enough Access then:

a) they know that you cannot secure Access totally
b) they have a copy of the bypass key enabler
c) once they are in the front with a bypass key they have full unimpeded access to the data

Yes you can take measures to keep out the curious but you wont stop a determined and able person
Your biggest threats are your own users and the security of your network

Microsoft have always claimed that if you forget the password then even they cannot get in so how come Passware works so many times

Sorry I cannot offer more comfort

regards
Paul

The nice thing about the way i have used the above script is that it disconnects from the databases on exit of the application. So if they bypass on the MDE they are not mapped out to the drive or the folders. In this way they would have to know a lot about where the data is stored and how to get to it. When the loading script runs (not visible on the MDE) then it reconnects to the tables and everything is good. This really helps me keep my data secure. And with the PW on the backend it also helps me keep the data secure if they do find it on the sared drive. So shy of putting it on a SQL server it should be working wonders now I hope. I have never had anyone break my database or mess with the data so far. I just want to keep it from happening in the future. Thanks again for all your assistance I appreicate all the info you have no idea how much help you have been.

You could consider this also,

in the users home directory you put a batch file instead of a copy of the front

they click on the batch file it copies the front (grom a master) to their directory and fires it up ,

when the user closes their copy of the front, the batch file gets control back and deletes the copy of the front

Even if they manage to break the front, they get a brand new one each time and no trace of it after they closeit

regards
Paul