Best way to connect to BE database

[bodylojohn]

Hello my helpfull friends,

I am going to build a multi-user database with the tables en queries stored on a server. So I will deploy multiple front ends.

Here is my question:

What is the best way to connect to the BE?
Should I link the backend using the linkingmanager (bound to a recordset) of access 2007

OR

should I interact with the data in the backend using undbound form using a disconnected recordset

OR using SQL

I would prefer the first option because it's the easiest way.

But what do you guys (and girls) recommend?

 

[RuralGuy]

If you are going to use Access as the FrontEnd of your application, you loose most of the power by not using bound forms.

 

[bodylojohn]

If you are going to use Access as the FrontEnd of you application, you loose most of the power by not using bound forms.

You are right.
That is where the power of ACCESS lies.
But how can i make the user choose the database without the ribbon and linked table manager???

 

[RuralGuy]

I don't have ac2007 but I have to believe it is similar to the way it is done in prior versions. Maybe someone with ac07 will drop by and help here.

 

[Rabbie]

This link may help you.

 

[MarvinM]

But how can i make the user choose the database without the ribbon and linked table manager???

Code I found to kill the ribbon:

        DoCmd.ShowToolbar "Ribbon", acToolbarNo
        DoCmd.SelectObject acForm, "frmResp", True
        DoCmd.RunCommand acCmdWindowHide

Throw that into your AutoExec module.

 

[MarvinM]

Is there a way to mark these threads as "Resolved"?

Just want to let you all know (from the OP) that this is resolved and in case any newbies find this thread in the future, here is what I did.

The best advice was from Bob,

Just link your backend tables using Linked Table Manager

Although the users have different drive mappings for the back end database, I was able to put the UNC path in the Linked Table Manager and all works fine.

Secondly, I use a lot of RecordSets to find and edit records. I have been advised that executing SQL commands will be faster. I need to work on that.

I also need to work on limiting the number of records I pull out of the BE. For each user, I typically pull all the records that apply to them. The idea was that they could review and make changes to the records locally and then send them all back to the BE at one time. It turns out that the users may browse through the records willy nilly, but when they make an edit, they send it in immediately. So there is more I/O across the VPN anyway. So again, I need to follow Bob's advice about limiting the number of records. I just don't have a lot of practice at that (yet).

4) Rabbie provided a link to a special function to refresh the links. It looks like it is particularly relevant to multiple back end databases. That isn't an issue here but it looks like it could be useful in other applications. Thanks.

Finally, that little code snippet in my 12/11 post is very helpful in preventing the users from messing with the form design, tables, etc. When we go to production, I can make an .accde file, I know, but during testing, they are using the working version. The first and second arguments in the SelectObject are your choice. I picked acForm but you can choose any of the object types that exist on your Nav Pane. For the second argument... It's optional in A2007 if the third argument is True. But, I had users with A2010 and they were seeing the Nav Pane. So I inserted a form name, e.g. "frmResp", and they stopped seeing the Nav Pane. There must be some change with A2010 that makes this no longer optional, but I haven't found any documentation on that.

Thanks to All and I clicked on the Thumbs Up for Bob since he led me in the right direction.