different addresses of Backend for different clients

[Happy YN]

Some of my clients have their BE on a local directory & some on a server.
Can I code the VB on startup to look for the BE in a different location depending on who they are?
Thanks

 

[Jack Cowley]

The MS Knowledge Base has a number of articles on the subject of re-linking tables and the Solutions.mdb (available as a download from MS) also has code to re-link tables.

hth,
Jack

 

[Pat Hartman]

Solutions Sample

There have been many examples posted here regarding relinking the be tables. Some use the Common Dialog API to open the file dialog to allow the user to find. You can get a working sample by downloading the solutions sample db.

http://msdn.microsoft.com/library/d...n-us/bapp2000/html/mdbdownload.asp?frame=true

 

[Happy YN]

Thanks for bothyour replies. I downloaded and scrutinised the MS example in solutions.mdb. This requires user intervention in pointing to the correct location of backend.
I would like to write a small ini file which resides on each users pc. It would contain the users backend address. As access starts up I want it to go into the ini, collect the backend address and relink and/or refresh etc.
Is that really such a bad method and how can I get access to read the relevant section of the ini file?
Thanks again

 

[Jack Cowley]

Save the path in a table.

 

[Happy YN]

No!!!
That won't work because that pathis different for every user and when the program loads up it will not know who is the user unless I tell it (e.g. by reading information on the BE) and it cannot read the information before it knows where the backend is! so wer'e going round in circles!!
Are you suggesting I make a seperate db with a table containing path direction for every user??
Are we access users so addicted that everything must be access & tables ?(no offence intended!) Surely a small ini file would suffice?

 

[Jack Cowley]

I don't know anything about .ini files so if you can create one that knows where everyone's BE is without you telling it then I say go for it because to use my suggestion you will have to tell the program where the BE is the first time you/they use it. I know you are against the Common Dialog box but what do you do if the user moves their BE?

 

[Happy YN]

the ini file would be a small file unique to each user.
The user mustn't move his backend! He musn't even know it exists!!!

 

[Jack Cowley]

Hmmmm. If you know how to read an .ini file from Access then I would go with your solution. You could also use a .txt file, but go with what works for you.

 

[Pat Hartman]

I would like to write a small ini file which resides on each users pc.

- so go ahead. The code I pointed you to can be modified to read the .ini file. You can write code that runs in the startup form that checks for the .ini file and if it is not found, opens a dialog that creates it. Rather than using an .ini file, you can force the be to be in the same directory as the fe. CurDir() returns the path to the current directory. CurrentProject.Path will return the name of the .mdb file and the full path to it. I don't see a question in your last few posts. Were you asking for someone to code this for you?

 

[Jack Cowley]

Pat -

Boy am I glad to see you! Thank you for jumping in as I did not know what to suggest next and, as always, you got right to the heart of the matter.

Thanks!

Jack

 

[Happy YN]

Yes Pat I am also mighty glad to see you although Jack has been helpful
Yes I was looking for someone to show me how to exract the path from the ini and teach access to link to it.
However your wise words have shown me a seemingly simpler solution and that is to always have front and back end in same directory and force fe to look for be in that directory -incidentally what would be the code to force this?
Having accomplished this I could always install the fe & be anywhere provided they are in same directory.
What are your thoughts on this?
Thanks again to all!!

 

[Jack Cowley]

Happy YN -

I always defer to Pat as she has forgotten more than I will ever know about Access. A legend in her own time and deservedly so....

Good luck with your project!

Jack

 

[Pat Hartman]

I don't have any sample code to post for you. Take a look at the InStrRev() function. It works like the InStr except from right to left instead of left to right. This should help you to isolate the path from the string returned by CurrentProject.Path

It's been a long time since I looked at the solutions code but I think it handles the basics. The help wizard should walk you through creating the process.

 

[Happy YN]

Thanks Pat
I don't think i will need to isolate the path if I put the be in the same dir as fe. I could just say it is in the same directory
I will try and extract the rest of the code from the solutions db
thanks again for all the help
A Happier YN!

 

[Pat Hartman]

Have fun.

 

[ChrisO]

This may help.

In the attached demo there are two databases, a FE and BE.

FE name is MyDatabase_A97.mdb and BE name is MyDatabase_A97BE.mdb

It will work if both are placed in the same directory and have the same file extension.
If you keep the BE named the same as the FE but with the addition of BE you will be able the rename them to whatever you like.

Hope that helps or at least gets you started.

Regards
Chris.

 

[Happy YN]

Hi Chris
Downloaded your db (I am using access 2000) but the autoexec gets stuck. do you mind to check it please thanks

 

[ChrisO]

Gets stuck?

This is a workable solution. It has been for seven years. I can not test it under different versions of Access. It is simply a demo… a guide. It requires work by those that may wish to use it. Under no circumstances should you use it until you understand how it works.

It warrants testing, it warrants poking, it warrants destruction if you can, it warrants the full force of your understanding.

Simply play with it till you understand how to maintain it. Please do not use code which you may not fully understand.

In the long run, the demo may prove to be more than just a simple answer…it may prove to be far more than that which you require.

Gets stuck?
How does it get stuck in Access2000?
What does it do in Access2000?
Where does it fail?
How does it fail?
Under what particular circumstances does it fail?

This code has been cut down from a demo that was written for, I suspect, a person that wanted to link multiple BE's across the WWW. (FE's in the US, BE's (plural) anywhere in the world.) It came from having to write 10 FE's for 170 BE's. That much is easy.

I, like a lot of other people on this site and many others, do not want to supply complete solutions.
It is your duty to try and understand.

This may seem hash, but please ask specific questions… not for total solutions.

I may get my backside chewed for this reply…but that is my position.

Kind regards,
Chris

 

[Happy YN]

Chris I am not insulted. I did not expect any full solutions. I am quite happy playing around and modifying any code. I am merely informing you that when I opened your db in access 2000 whether I converted it fully or whether I opened it 'as is', it came up with an error saying the macro autoexec does not work . On debug it stops on the line
MsgBox "Attaching Database... Can't find " & rstCustomerTables!TableName
I am fully aware that the forum is not for spoonfeeding!
Thanks again
Happy YN