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

[AdamOnAccess]

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

[Stewart Ross]

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

[beacon]

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

[DonRayner]

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

[AdamOnAccess]

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

[beacon]

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

[Stewart Ross]

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

[AdamOnAccess]

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

[ChipR]

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.

Function ReLink() As Boolean
On Error GoTo ErrorHandler

Dim db As Database
Dim tdf As DAO.TableDef

    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & PathBox
            Err = 0
            On Error Resume Next
            tdf.RefreshLink ' Relink the table.
            If Err <> 0 Then
                ReLink = False
                Exit Function
            End If
        End If
    Next tdf
  
    PathBox.Visible = False
    
    db.Close
    Set db = Nothing
    
    ReLink = True
    
ExitCode:
    Exit Function

ErrorHandler:
    HandleError Err.number, Err.description, Me.Name & ":Relink"
    Resume ExitCode
    
End Function

[DonRayner]

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

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

mysqlstring = "Select [" & Me.EmpSelect & "].* , Skills.TrgDoc " & _
              "From (" & Me.EmpSelect & " INNER JOIN SkillRequirements ON [" & Me.EmpSelect & _
              "].TrgItem=SkillRequirements.TrgItem) Inner Join Skills on [" & Me.EmpSelect & _
              "].trgitem = skills.trgitem " & _
              "WHERE (((SkillRequirements." & myname & ")=True));"
        
[Forms]![employees].Form!subform1.Form.RecordSource = mysqlstring