Upgrading from Access 2003 to SQL Server 2008 Express (1 Viewer)

[speedball]

I currently have an Access 2003 database split between a front and back end.

When a user wants to edit a particular record, they click a button and the record is copied from the backend table to a temporary table on the front end, which they can edit. And on the backend, one of the fields on the record being edited is marked as ‘Locked’ to prevent another user from editing the same record. Once the changes are made the record gets updated on the backend table, and the front end table is cleared.

This is all done with a combination of VBA code and SQL statements.

This all works okay, but my database is getting large and the number of concurrent users is ever increasing and so it is starting to get quite slow. So I want to upgrade to SQL Server 2008 Express. I would also like to switch to Access 2010 for the front end, mainly for aesthetic reasons.

I’ve been going through some tutorials on SQL Server 2008 and I assume that Access 2010 will be similar enough to Access 2003 that it won’t cause any major issues. But where I’m struggling, is to understand the following:

1. How will I replace the process of copying records from the back end to the front end? Will I be copying records from a table in the SQL Server back end to a table in the Access 2010 front end? Or will I need to rethink the database logic?

2. I understand that Access SQL statements will need to be rewritten as T-SQL statements, but what about the VBA code?

Thanks in advance
Speedball

 

[SQL_Hell]

1. Yes considering you already have this record locking methodology in place, then it wouldn't be too much work to copy from the SQL server backend to a local access table, it would be basically the same as your doing now apart from your backend table would be a linked sql server table and your inserts/ updates could be done via a stored procedure

Do you get users updating the same records often then?


2. The VBA code will need to be updated to open ADO connections and issue ADO commands to the sql server database if you are using DAO.

 

[speedball]

Thanks SQL_Hell

1. If I copy from the SQL Server backend to the Access 2010 frontend, will I still be getting the full benefit of having SQL Server as the backend?

All users have access to the same overall data but usually only update records that are relevant to their work, and there isn't that much overlap. So there are ocassions when two users will want to update the same record and this might be at the same time, but it is not that common.

But it is common that multiple users will be viewing the same records at the same time.

This is my first database so the method that I've used probably isn't optimal. When I switch to SQL Server I'm happy to redesign the whole database so if you have any alternative suggestions, that would still prevent two users updating the same record at the same time, I'd be grateful.

2. Okay, thanks. I'll look into that further.

Thanks
Speedball

 

[SQL_Hell]

Ok in that case I would do away with the copying to a temp table and just use unbound forms and a stored procedure to get the data and stored procedure to update /insert.

Once a user decides to edit the record you could refresh the data using the select stored procedure to ensure they have the lastest updated record, that way you wont be overwriting

 

[speedball]

Thanks SQL_Hell

I don't know how to get the values from a table into the controls of an unbound form

Suppose I have the following:

- a table on the backend called StockList where I want to edit a field called UnitPrice on one of the records

- an unbound form on the frontend called EditStockDetails with a textbox control called UP

How would I go about getting the current value of UnitPrice into the textbox called UP?

I also won't know how to get the updated value back into the StockList table, so if you could let me know how to do that as well, that would be great.

Thanks very much for your help
Speedball

 

[SQL_Hell]

I am a bit rusty on the Access side of things because I have only been doing SQL server stuff for a number of years now

But I found you an example of using an unbound form

http://oakdome.com/programming/MSAccess_Unbound_Forms_VBA.php

 

[Shado]

Use views. They operate the same as tables. Working on a similar situation at present.

 

[SQL_Hell]

Use views. They operate the same as tables. Working on a similar situation at present.

 

[speedball]

SQL_Hell: Thanks for the advice and for sending through the link. I'll look into this further. I've also reached the part on Stored Procedures in my SQL Server tutorial (from Lynda.com), so your suggestion on that is starting to make a bit more sense. Thanks.

Shado: Thanks for posting, but I'm not sure what you're referring to. Is this something in Access or SQL Server? Perhaps you could elaborate?

Thanks
Speedball