Sharing Information (Import/Export Forms & Reports)

[Sydcomebak]

I live in TX, my dad lives in FL. I am writing him an Access DB and just recently attached the latest version to an email. He DLed it, and got right to work populating data and printing reports... but...

He needs a new form, which I have now created. I want to send him this new report (and the form that links to it) but if I send the entire DB, he'll lose any data that he has already typed in.

How do I just send the form and report he needs and import that into his version of the DB?

Thanks everyone!

-Dave

 

[MarkK]

He can send you his DB. You can send him a DB with just the objects he needs to import, and he can import them.
The typical solution however, is an architecture called a fe/be, or front-end/back-end, split. In this arrangement there is a data file (be) and a program file (fe), and the program file contains links to tables, not tables themselves. This makes it really easy to replace the whole fe with updates and just leave the be alone.
Cheers,
Mark

 

[Sydcomebak]

Dad's not tech savvy, and The DB is probably 20+MB by now so sending it back and forth is pretty time consuming and fills his outlook boxes. How do I set up a FE/BE Split? Is it easy to split apart a 1 part DB into this arrangement?

 

[Sydcomebak]

Found this:

To split the database in Microsoft Office Access 2007, follow these steps:

1. Create a new blank Access database.
2. On the External Data tab, click Access in the Import group.
3. In the Get External Data dialog box, click Browse to locate and select the database that you want to split, click to select the Import tables, queries, forms, reports, macros, and modules into the current database. check box, and then click OK.
4. In the Import Objects dialog box, click Select All on the Tables tab, and then click OK.
   
   Notice that Access imports all of the tables into the new database, which is your back-end database.
5. Store the new back-end database on a network share, and make sure that all the users have full permissions to the share.
6. Create a second new blank Access database.
7. On the External Data, click Access in the Import group.
8. In the Get External Data dialog box, click Browse to select the back-end database that you created, click to select the Link to the data source by creating a linked table. check box, and then click OK.
9. In the Import Objects dialog box, click Select All on the Tables tab, and then click OK.
   
   Notice that Access links the tables in the back-end database to the front-end database.
10. On the External Data, click Access in the Import group.
11. In the Get External Data dialog box, click Browse to select the original database that you are splitting, click to select the Import tables, queries, forms, reports, macros, and modules into the current database. check box, and then click OK.
12. In the Import Objects dialog box, click Select All on the Forms tab, repeat this step on all other tabs except the Tables tab because you have already linked to the tables. You now only need to import the rest of the objects, and then click OK.
    
    Notice that you now have all the tables linked and have imported the remaining objects.

I'll give it a shot.

 

[MarkK]

If the file is getting too big check out Compact and Repair. Temporary data structures are sometimes created by Access and not deleted, and it is normal maintenance of your Access files to Compact & Repair from time to time.
Also, splitting your DB will reduce the size of the file.

 

[smig]

This is why splitting your db to BackEnd/FrontEnd is so importent !!!
You can update the frontend with out touching the backend (The data).

Now, as you probably didn't do the split, you'r stuck. He must send you the db with the data.
Just RAR the file. it will reduce it drasticely. Packing it as suggested before will even help more.

After you add what you need do the split

 

[Sydcomebak]

I have successfully split the DB and things are working great! Sometimes, We're having issues after I send him the updated FE/DB. None of the forms work until after I go through a full recompilation. Not a huge deal, but why would that happen?

 

[smig]

you might need to relink tables in the user updated FE

you can use this code:

Dim CurrDB As DAO.Database
Dim tdf As DAO.TableDef
 
Set CurrDB = CurrentDb()
 
'--- Loop through all tables in the database.
For Each tdf In CurrDB.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
        tdf.Connect = "MS Access;PWD=" & [B][COLOR=darkred]DataMDBFilePass[/COLOR][/B] & " ;DATABASE=" & [B][COLOR=darkred]DataMDBFile[/COLOR][/B] & " "
        tdf.RefreshLink         ' Relink the table.
    End If
Next tdf

 

[MarkK]

Describe this in more detail...

None of the forms work

That could mean anything.