How to work on home laptop, then update client's Front End

[lorirobn]

Hello,

I am in the process of bringing my Access application to client's server, and need some help!

I need very simple basic instructions on the procedure to follow for UPDATING the front end application, so that I can work on my home laptop and make changes, and then bring the updates to the Front End on one test computer? If I simply copy the updated .mdb onto the workstation and overlay the original, I was told (by IT person) I would have to re-link the tables to point to the server, and this would be a bear. So he walked me through his suggested procedure: open the existing database on workstation, and do a File--->Import of all objects from updated version. This was a LOT of work, and prone to errors, as Access renamed the new objects (by appending a '1'), so I had to delete all original objects, and rename the updated objects. This cannot be the way to do it ongoingly. I get that there is a way to automate updating the application from the server, but I haven't read anything about linked tables and how to go about re-linking from laptop links to server links.

On my laptop, I have Front End and Back End linked via 'linked tables', but client's linked tables are on the Server.

Should I be working on my laptop via MSDE, or something that mimics a server?

Background info, if necessary, is: I created application(mdb) in Access 2003, converted it to Access 2000, split it into Front End and Back End, and IT person brought Front End onto one test computer, and brought Back End onto MS-SQL Server 2000. Also brought copy of Front
End on the server, as a backup. When he copied front end and back end from my laptop, he had to do some work to change the linked tables'addresses, to point them to the server.

I am sorry if this question is repeating what's already out there. I saw a lot of postings on automating the update process, but they didn't mention linked tables and I suspect
that changes things. Also, I haven't hooked up my laptop to the server yet.

Thanks in advance for ANY help!!!
Lori, former COBOL programmer and new to MS-SQL Server...

 

[dhookom]

This is a fairly common need. There is code at

http://www.mvps.org/access/tables/tbl0009.htm

which can be used to update the connection properties of your linked tables. I normally create an unbound splash screen/form that calls the function. If the linked "location" is not available, the user is prompted for the new back-end location.

You may need this code also

http://www.mvps.org/access/api/api0001.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alvechurchdata]

I was told (by IT person) I would have to re-link the tables to point to the server, and this would be a bear.

I agree it's a pig of a job by hand but if you've use the Linked Table Manager it's just a case of clicking a button.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lorirobn]

Hi Duane,
Thanks for your reply, and for helping me out once again.

Before I look at the code, I want to back up and ask some very basic questions. So - to begin to update my database, is it correct to copy the new .mdb version in from my laptop and overlay the old on the workstation? Then, run the code that you gave me (tbl0009) to update the links? I notice the code mentions to run this every time the db opens - do you suggest this as well, or is it enough to do it just when I make updates?

What is the 2nd code for?

Would it be common practice to put the updated front end on the server as a backup, then copy it to each workstation from there?

Sorry, I've got to get past these basics before I can move on to the code part!

Thank you,
Lori

 

[lorirobn]

Thank you Geoff,
You answered my question on another forum, too!
Can you explain how to use Linked Table Manager, to change link from my laptop's 'back end' to MS-SQL Server's? Is there a way I can practice this on my laptop, before doing this at client's site and attempting this on the server?

Also, does it matter if client uses ODBC to connect?

Thanks,
Lori

 

[dhookom]

Sorry, I missed the part about using SQL Server as the back-end. If this is the case, I would recommend installing MSDE on your laptop. This will allow you to program using the more powerful features of SQL Server.

There is other code at

http://www.mvps.org/access/tables/tbl0010.htm

that allows the user to select from different ODBC/DSNs.

You can also do this manually. Assuming you have MSDE installed and your tables are linked from your local MSDE database.
1) take a copy to the network where the application will be deployed.
2) start the application while holding down the shift key
3) select Tools->Database Utilities->Linked Table Manager
4) check the box to "Always prompt for new location" and select all tables
5) Select Ok and then find the Data Source used at the client (these should be set up and consistent)
6) After the new Data Source has been set, copy the front-end to all users.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lorirobn]

Thanks for the step-by-step, Duane.
Once I install MSDE on my laptop, do I put my back-end on it? And keep my front-end as is, but re-link the tables to point to MSDE?
Lori

 

[dhookom]

You would move your tables to MSDE. This can be done in a number of ways depending on the number of tables etc. A simple method is to first create a database on MSDE with the same name as the production database. You can then create a DSN that points to this database. Each table can then be exported to the database and linked back into your front-end. You will want to rename the linked tables.

You may also want to open the MSDE database using SQL Enterprise Manager to set some table properties such as referential integrity etc.

Depending on your installation of Access, you may be able to use the Upsizing Wizard.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lorirobn]

Duane,

I have started to install MSDE, but I can already see that the setup and installation, and learning it, will be quite time-consuming for me. Would you say that it's fairly important for me to have this on my laptop? You seemed to suggest it because of the SQL Server backend. At this point, I am nearly done with my Access application, and am in the stages of getting onto client's server and testing it on there. I'd hate to start learning/setting up new software at this point, when I'd rather be 'winding down'. But I do want to properly get the app. up and running at client's site.

Thank you,
Lori

 

[dhookom]

You should be able to link your tables to Access while using SQL Server at your clients. You miss out on lots of SQL Server functionality with Access tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alvechurchdata]

Can you explain how to use Linked Table Manager, to change link from my laptop's 'back end' to MS-SQL Server's

In a word - 'No'. I suppose you could run your tests with Access tables on your laptop then link in SQL tables of the same name when you install it but I would expect there to be subtle bugs because of the different data types and the problems of permissions in SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lorirobn]

Geoff,
Not sure what you are saying. Are you saying I should not mix Access tables with SQL tables, just as Duane above has said, when he suggested putting MSDE on my laptop? When you suggested Linked Table Manager, did you not realize I had Access tables on my laptop?