Help! Split Database Workaround - Connecting to BE and Dumping Data?

[slkasulke]

Hi.

I have a split database that has a backend that resides on a server for my company. People in one building can access everything just fine with no speed issues, however the building I reside in does not have the privileges having updated networking.

Looking for workarounds. Working locally will be the best option. Is it possible to connect the database's front end with the backend at startup, import all of the data from the server into my tables, and then close the connection again? On the front end, I plan to have a button that syncs data by connecting to the backend, updating the tables with any changes, and disconnecting. If so, any tutorials out there or examples? Also, any other suggestions?

Sorry if this is in the wrong part of the forum.

Thanks.

 

[AccessMSSQL]

Is there any share that both buidlings can access? I would suggest moving the Data file to a location where both buidlings have access. Other than that sounds like a messy job.

 

[slkasulke]

Is there any share that both buidlings can access? I would suggest moving the Data file to a location where both buidlings have access. Other than that sounds like a messy job.

Both have access to the same shared server. The problem is that one building has a very slow network connection, whereas the other has a speedy one. It takes three minutes to load forms off of the slower network, but loads almost instantly over the quicker one. It's even slow when the front end is located in the same folder on the shared server. There is no option to just use the databse in the building with the fast connection. I think messy is something I'm going to have to work with, since my tools are limited.

 

[AccessMSSQL]

Try this link -
http://www.devhut.net/2010/06/10/ms-access-vba-import-external-database-tables/

How many users are on the slow connection? Are you planning on having every user work locally? Or is there somewhere you can put a 2nd copy of the backend that your users in slow building can link to - so you will only have 2 copies of the back-end? How do you plan to sync the data or determine which records have changed/been added on both sides? Or were you also looking for examples on how to do that.

 

[slkasulke]

Try this link -
http://www.devhut.net/2010/06/10/ms-access-vba-import-external-database-tables/

How many users are on the slow connection? Are you planning on having every user work locally? Or is there somewhere you can put a 2nd copy of the backend that your users in slow building can link to - so you will only have 2 copies of the back-end? How do you plan to sync the data or determine which records have changed/been added on both sides? Or were you also looking for examples on how to do that.

I can't say for sure... I would estimate at least 500 use it daily. There would be only a handful of people using this database in question. I would prefer every user to be operating the same way. Yes, I would like examples on how to sync the data on both ends, if you could dig something up for me.

 

[AccessMSSQL]

I think your best method to do this is to work with unbound forms and temporary tables. It sounds like most of your forms are bound. You would have to modify the front-end forms - users would have to search for a specific record. The front-end would then find that record in the main database - insert the data into a temporary table that has the exact same structure as the live table and then let the user modify the data in the temporary table. When they save it - the data gets saved back to the main database. I would also recommend creating a check-out routine. If a user pulls up a record for EDITING it would be locked to other users until the editor saves the record at which point the data gets saved back to the main database.

You will have to create temporary tables for each table being modified. Every time a record gets checked out the data from the temporary tables (which are local tables) will be deleted so you will always only have one record in temporary tables. Logic: User searches for record - delete data from temporary table , insert record selected by user into temporary table > open form bound to temporary table > user edits record and clicks save button > data in temporary table is saved back to main live table.

When user checks out record you can set a flag in the record (checkedOut) which will be a yes no field. Set it when the data gets loaded into the temporary table. In the search routine if a different user tries to check the record out - just do a check in VBA for the flag. If it's true, don't let that user check the record out.

Hope this gets you started.

 

[AccessMSSQL]

What version of access are you running in? I can send you a quick and dirty example.

 

[AccessMSSQL]

Here is a simple example of using temporary table with an unbound form. The example has a simple search form - there are only 3 records in the linked table (you will have to re-link the table so it matches your path. Just examine the code behind the forms.

this is Access 2010.