Record Source on a Form (Access 97 SR-2)

Topic

New Reply

We use a database on our network. I have a make table query that makes a table in a database table on the C: drive. I would like for the record source of a form to be from a different database than the networked database the form is in. Can this be done?

Reply | Quote

Replies

Are you saying that you want a form residing in a database on the network to link to the table residing on your C drive? Usually people don’t have access to other peoples hard drives, so the form would only work for you. Anyway, you can import (as a link) the C drive table into the networked database, but if this data is only for you, you could instead, import the form from the network into the database on your C drive. Have you considered having a front end database on your C drive, containing the queries and forms that you need; and just link to the tables in the database that resides on the network?

Reply | Quote

Yes, that is what I want to do. What I am trying to set up is to allow users to update their own records without letting them have access to other user’s records. I thought that if I had a MakeTable query make a table on their C: drive that only contained their records and then have the Record Source of a form that is on the network be set to their table on their C: drive, I could let them update only their records. I can’t figure out how to refer to the C: drive database. (In Paradox this would be easy to do ).

I don’t know if I have a front/back end. What I have is a database on the network that contains the tables and a database on the network that contains all the forms, etc., with links to the tables. I think this is called a split database. If I can’t do what I want (as explained above), I may have to push the database with the forms and links to their C: drive and go from there. I think that would actually be a front/back end arrangement.

Edited: What I need is to know is how to write the path in the Record Source to the database on the C:drive.

Reply | Quote

Lonnie,
If you have your database on the server, and you want to link a table on the c: of a networkstation, you’ll have to make a link for every user (networkstation) with a different table name (link). You cannot link one table to different sources at the same time.
How will you take care of that ?
Seems very complicated even if it is possible.
I think it would be better to add a user field to your table(s) and filter the tables with the currentuser()

Reply | Quote

Thank you for the response. I am not going to give up, quite yet.

Reply | Quote

I agree with Francois. And just for info, the split network database you described, is a front end/back end. The actual tables are in the back end. The front end is linked to the back end tables, and is normally what a user opens. I would forget the C drive (unless you just want to place multiple copies of the front end on each user’s desktop). Of course then you have multiple copies to maintain (or replicate and synchronize). If there aren’t too many users, and the front end does not contain any tables (other than linked), you can make changes to one copy of the front end, if necessary, and then simply replace each user’s copy with your new front end. Warning: if your users are allowed to create/change queries, forms, and/or reports, then they really do need their own copy of the front end. I apologize if I’m straying from the original post.

Reply | Quote

Thanks for the front/back end explanation, willyboy. I still think what I want can be done, unless Charlotte says it can’t.

Reply | Quote

There are very few things that actually can’t be done. There are, however, a *lot* of things that shouldn’t be done, and this is one of them.

You could create a local table (Paradox doesn’t keep all its tables in a single file the way Access does) in a database on C:, but that wouldn’t allow you to update your network database; and you don’t really want to take on all the manual administration that would be involved in updating the network database from the individual front ends.

You would be better off filtering the records they can see based on a user login, either a network login or using Access security. It would require some (or a lot of) code, but you wouldn’t be faced with having to fold their edits back into the master database.

Still another option would be to use partial replication and distribute partial replicas to each user that contained only that user’s records. You can download a partial replica wizard from the Microsoft site.

Reply | Quote

I give in. Thanks Charlotte. I didn’t see the problem with the C:drive table not being connected to the main tables.

I do have code to filter the database so that users only see their stuff. I think it will work well. I will look at the partial replica route, also. Again, thanks and thanks to Francois and willyboy.

Reply | Quote