By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Working with 2 MDB files - One for Forms, One Holds Data

P: 99
I've programmed Access before but everything was always been contained in one MDB file. Now I would like to separate the forms, queries and reports in one MDB file, and maintain the database tables and the actual data in separate MDB files.

In this way, I'm hoping I can maintain data for many clients in separate MDB files, but only maintain one program file. If any client wants their data, they can have it and I still get to keep my software.

Can anyone tell me how I can learn to do this?

Thanks,
Adam
Nov 21 '08 #1
Share this Question
Share on Google+
9 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You may find the following article on splitting the data from the front end in our HowTo section helpful: Front End/Back End.

-Stewart
Nov 21 '08 #2

beacon
100+
P: 579
Hi Adam,

I could be off base on this, but I think you want to look into splitting your database. As long as you are working on a network or have access to a separate server, you should be okay.

Basically, and you're going to want to search this to make sure I'm right (maybe even try it with a dummy database first), you'll go to Tools -> Database Utilities -> Database Splitter. This should allow you to put the data and tables on the back-end (on a server or a separate network folder) and the forms, queries, et cetera on the front-end.

Once you done this, you're probably going to want to setup permissions for users because I think that by default the user will be able to modify the forms, queries, et cetera because Access thinks that everyone that opens the database is the Administrator unless people are specifically defined in the database.

I've only done this once and it's been FOREVER, but I'm actually working on another database now that I'm eventually going to need to do the same thing that you've described.

I hope this helps point you in the right direction...

beacon
Nov 21 '08 #3

DonRayner
Expert 100+
P: 489
Beacon is correct in the method for splitting an already existing database.

If you are starting the creation of a new project you just make one mdb database (MyDatabase_Be.mdb) and build all your tables in there. Then create a new database (MyDatabase_Fe.mdb) for the actual forms/reports/queries..etc. All you need to do is to link to the tables in the first database.

Select tables in your database window and right click in the table area. Select link tables from the menu and then navigate to the location of your (MyDatabase_Be) mdb file. Select the tables you want to link to and then OK to perform the link.

Thats all there is to it.

Don
Nov 21 '08 #4

P: 99
Sounds easy enough. Thanks All.

Actually, I'm not on a network. I do some freelance marketing and I want to set up a situation so if a client leaves, I can give them their data but I do not have to give them my programs.

My only concern is what to do if I want to make a change to any one database. If might mean I have to make a matching change to all.

Is there a better way to do this?

Thanks,
Adam
Nov 21 '08 #5

beacon
100+
P: 579
Sounds easy enough. Thanks All.

Actually, I'm not on a network. I do some freelance marketing and I want to set up a situation so if a client leaves, I can give them their data but I do not have to give them my programs.

My only concern is what to do if I want to make a change to any one database. If might mean I have to make a matching change to all.

Is there a better way to do this?

Thanks,
Adam
Hi Adam,

If you're using your personal computer to create the databases, the idea of putting the back-end and front-end databases in separate locations can still be achieved on your personal computer. This might actually be easier for you because you don't have to set the permissions I mentioned before.

I guess in regard to your question about the database changes, it depends on how many databases you create and how many differences there are between them. If you have setup one database that serves as the template for all others, I think you could, after splitting the database, make changes to the template and then copy the changed forms, queries, reports, et cetera to the existing databases.

Again, I could be wrong...I'm answering your questions to help make sure I understand it as much as I'm hoping to help you, if that makes sense.

Anyway, good luck and keep the questions coming if this doesn't help...

beacon
Nov 21 '08 #6

Expert Mod 2.5K+
P: 2,545
Hi. If you wish to prevent users from seeing your VBA code you can convert your MDB file to MDE (for Access 2003) or ACCDE (Access 2007) which has VBA code removed.

-Stewart
Nov 22 '08 #7

P: 99
Hi Again,

Stewart, thanks for the idea but it is not the code I'm concerned about as much as the techniques I use to maintain the data. I have some unique approaches to maintaining this type of data and I simply wouldn't like it to get out. The client is entitled to their data, but the programs & techniques belong to me.

I've played around with splitting a database and I understand how it works, but I'm looking for something slightly different.

The approach of splitting the database in Access seems to still lock the front and backends together. Clearly, the idea of splitting was to place the backend on a server and allow many different frontends access to it. I'm trying to do the opposite.

Ideally, I would like something that works like this...

I have a frontend database that contains a table called "backends". The backend Table contains the name of MDB files; names like client1.mdb, client2.mdb, etc.

In the frontend database, I bring up a form with a dropdown box. The dropdown box shows me a list of all the backends that I could "connect" to. This list is generated from the backend table I described above. I select the backend mdb file from this list, and now my frontend knows which backend to draw data from.

Is there a way to do this?

Thanks,
Adam
Nov 26 '08 #8

Expert 100+
P: 1,287
Do all the backend files you want to connect to have the same tables? It's pretty easy if they do, if not it's going to be complicated.

Here's what I use to relink all the tables that have connection locations to a new location. Pathbox is a text box on my form that the user enters the path to the backend db in. You could easily get your path from a combobox with columns for description and path taken from your table.

Expand|Select|Wrap|Line Numbers
  1. Function ReLink() As Boolean
  2. On Error GoTo ErrorHandler
  3.  
  4. Dim db As Database
  5. Dim tdf As DAO.TableDef
  6.  
  7.     Set db = CurrentDb
  8.  
  9.     For Each tdf In db.TableDefs
  10.         If Len(tdf.Connect) > 0 Then
  11.             tdf.Connect = ";DATABASE=" & PathBox
  12.             Err = 0
  13.             On Error Resume Next
  14.             tdf.RefreshLink ' Relink the table.
  15.             If Err <> 0 Then
  16.                 ReLink = False
  17.                 Exit Function
  18.             End If
  19.         End If
  20.     Next tdf
  21.  
  22.     PathBox.Visible = False
  23.  
  24.     db.Close
  25.     Set db = Nothing
  26.  
  27.     ReLink = True
  28.  
  29. ExitCode:
  30.     Exit Function
  31.  
  32. ErrorHandler:
  33.     HandleError Err.number, Err.description, Me.Name & ":Relink"
  34.     Resume ExitCode
  35.  
  36. End Function
Nov 26 '08 #9

DonRayner
Expert 100+
P: 489
@AdamOnAccess

Here is a sample of some code that I use to change a forms recordsource on the fly based on the selection from a listbox. Basicly in my database each of the employees have their own table and I use this to switch tables. If you linked the appropiate tables from your customers databases into your frontend you could do something along these lines

Expand|Select|Wrap|Line Numbers
  1. mysqlstring = "Select [" & Me.EmpSelect & "].* , Skills.TrgDoc " & _
  2.               "From (" & Me.EmpSelect & " INNER JOIN SkillRequirements ON [" & Me.EmpSelect & _
  3.               "].TrgItem=SkillRequirements.TrgItem) Inner Join Skills on [" & Me.EmpSelect & _
  4.               "].trgitem = skills.trgitem " & _
  5.               "WHERE (((SkillRequirements." & myname & ")=True));"
  6.  
  7. [Forms]![employees].Form!subform1.Form.RecordSource = mysqlstring
  8.  
Nov 26 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.