Requesting advice on Linking Access Databases

[dataempress]

Hi All,

Here's my situation: I have an Access database for one specific program at my school. I have two other programs that I would like to enter information for into this database. To complicate this, the data has to be kept seperate sometimes, and sometimes it doesn't. It all depends on what type of report is needed.

What I'm wondering is, could I make three seperate databases for each program and connect them when I have to. Sometimes I have to detatch the main database and send it off to be evaluated, so that's why I can't really blend the three together. The information would be mixed. Also if I had three seperate linked databases, could I access them all from buttons on the start form, and combine data from them into one query when necessary? Or is there a simpler approach? As always, thank you!

[Ecniv]

Yeah you could.

Three mdbs and link to them as long as you have a path to follow (you need to specify or use a text file or something).
You'd need to look at ado schemas or dao (tabledefs and .connect)


Alternatively, put them all together and use filters/queries to show only information that the specific user is allowed to see.

ie use a log in screen and other thingsto filter the data.

send off the main database?

Cant you recreate the structure and use this as a template mdb. Copy the data in from the main query and send off a copy?

[dataempress]

Thank you for your answer!

I've heard of ADO and DAO, but I'm not sure what they are. I'm trying to look up tutorials so I can practice. The first tutorial I clicked on was ADO in web development, but does it have to be web related? My databases are not web based. They are located on an internal network server.

I could recreate the structure and use it as a template. That would just involve a Save As, and I'd save structure only, is that right? That was, in fact, what I'd planned to do.

I hadn't thought of a log in screen. Is there a special template for one in Access, or is it just a form with text boxes? All three programs have pretty much the same staff, so I wish I could just put all the info together, and just use combo boxes to filter the info, but one of our programs is sponsored and we have to send out all that info seperately.

[Ecniv]

I think there are tutorials for log in screens using access posted on this forum (somewhere).

They cover you in ADO/DAO connecting to tables, reading and perhaps writing data.


Q) What platform are you using...? All Access, Excel Front End / Access Back End, Some other front end / Access Back end?


If you stick with all access and access forms I recommend using DAO.
You can always change to ADO at a later stage should work allow bigger databases.

But I am biased as I work with Access more than Exhell

Do you have any books on Access? If not, look for the intermediate reference guides. Mainly because once you pass beginner (assuming you like it) you wont be back there very often. Intermediate also hints at things you could do and gives a reference guide for riminding yourself of things you have done.
I used sybex access 97 when I started out, and its laid out for easy reference. It did hint at bigger things, which the internet and forums come to your aid.

Answer my question above and perhaps we can get you to a log in screen stage...? It is for work right?

[Siddharth Rout]

Err, I wouldn't recommend DAO. In fact from Win 7 onwards, DAO is not supported...

[Ecniv]

Most companies over here appear to be using Win XP. So Window 7 dao shouldn't be a problem (check your target users machines?).

Isn't DAO inbuilt with Access anyway? Still there in Access 2007? Think it is...

Most companies are using 2003 anyway, so learn it for now. Its easy to switch to ADO, just ADO isn't as tied in to Access, more flexible in some ways, confusing in others