The proverbial: Where do I put my BE? (1 Viewer)

JoseO

Registered User.
Local time
Today, 12:20
Joined
Jul 14, 2013
Messages
72
Hello folks,

I got into access about 2 1/2 years ago and I am hooked! Love this program.

I have split DBs here at work with ease due to the network infrastructure allowing me to simply place my BE in a windows standard folder and simply link my FE to my BE with a simple DNS address - no problem.

Now, I am starting to use Access more at home and would like to split my DBs but I yet to really understand how to migrate my BE to the plethora of choices I keep reading about: Azure, SQL Server Express, etc.

Would someone be kind enough to point me to the "Fisher Price" way to begin doing this - really, you won't insult me. I really would like to approach/learn this as though I am a two year old being told what to do. :confused:

Thank you
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:20
Joined
Oct 17, 2012
Messages
3,276
Well, first of all, you can use another Access file as a back-end; that's actually the easiest method. The other options are certainly viable, however.

Now, regardless of which back-end you use, however, if you're doing it manually, you link to the back-end via whichever 'External Data' option your version has. For 2007 or later, it's a tab on the ribbon labeled 'External Data'. Then you click on either the visible button for what you want to pull data from (on mine, they are Access, Excel, SharePoint List, Text, and XML File), or if it doesn't appear, on 'More'.

The 'More' button pulls up a plethora of other options. If you want to hook into SQL Server, you use 'ODBC Database'.

You'll be given the option to either import or link the data - select link.

Now, for Access, you'll be given a standard Open File window. Navigate to the .accdb or .mdb file you plan on using for your back end. Double click it, then select the tables you want to link to, then Ok. Voila, you now have a front end and a back end.

For SQL, for manual connections, you'll need a DSN. You can create them by searching Windows (start menu for 7, search bar for 10) for 'ODBC', then running the Data Sources (ODBC) file that comes up. (The specific file name is odbcad32.exe.) You'll see a list of existing data sources - if the one you want isn't there, then select 'Add'. You'll get a list of drivers - you want whichever version of SQL Server Native Client you get. Click Finish, and a wizard opens up.

Fill in the Name (how you want it to show in the DSN list), a description (which I have never seen used), and the server name (which was created when the server was set up).

The next screen lets you choose between making the user login, or using Integrated Windows authentication, which is a fancy way of saying that the user's login ID is passed to SQL server, and the server checks the assigned rights itself. It's faster and less annoying than server authentication (where users supply login and PW), but more secure.

The next screen allows you to set the default database (it defaults to 'master'), and gives a few options. Leave them alone unless you are quite familiar with the program.

The screen after that is even more options, and again, just leave them alone until you're more familiar with SQL Server.

Then press Finish, and a confirmation screen with a data summary comes up. I'd always recommend using the Test button to make sure the connection works. You can also either save it or cancel.

That said, you'll also need to become familiar with whichever backend system you've chosen to use. VERY familiar.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:20
Joined
Sep 21, 2011
Messages
14,044
I split one of my home databases a good while back, but just to see how to do it.?

If it only for home use and single user?, then I would probably leave as it is unless size was also an issue?

My split DB BE exists in the same folder as the FE.

Hello folks,

I got into access about 2 1/2 years ago and I am hooked! Love this program.

I have split DBs here at work with ease due to the network infrastructure allowing me to simply place my BE in a windows standard folder and simply link my FE to my BE with a simple DNS address - no problem.

Now, I am starting to use Access more at home and would like to split my DBs but I yet to really understand how to migrate my BE to the plethora of choices I keep reading about: Azure, SQL Server Express, etc.

Would someone be kind enough to point me to the "Fisher Price" way to begin doing this - really, you won't insult me. I really would like to approach/learn this as though I am a two year old being told what to do. :confused:

Thank you
 

Ranman256

Well-known member
Local time
Today, 13:20
Joined
Apr 9, 2015
Messages
4,339
BE on a server,
FE to each user, usu on their personal server folder, for easy distribution.
 

JoseO

Registered User.
Local time
Today, 12:20
Joined
Jul 14, 2013
Messages
72
Thank you to all for your valuable feedback. I apologize for the late reply but it wasn't until Ranman256 replied that I received an email notification.

Again, thank you all for your feedback.
 

Users who are viewing this thread

Top Bottom