Access Front-End, SQL Server Back-End

whdyck

Registered User.
Local time
Yesterday, 18:42
Joined
Aug 8, 2011
Messages
169
I'm using MS Access 2003.

I'm wanting to switch my Back-End from MS Access to SQL Server Express. Just not sure the best way to approach this.

I've read that pass-thru queries to a SQL Server back-end can be very inefficient (sending the entire table of data over the network), so I'm wondering whether anyone has recommendations. Do everything through stored procs in SQL Server?

Is it a good idea to use DAO with an ODBC connection? Is there a better way?

Bottom line: My question is that for those of you who have done this effectively, what is your approach?

Thanks for any help you can give.

Wayne
 
You're reading from the wrong sources then. SQL Server doesn't send the whole table if you write your queries well.

Pat Hartman has posted quite a bit here about that.

Posted from my phone on Tapatalk
 
Firstly get SQL server up and running somewhere and ensure that your network DBA gives you rights to a test environment. They might be able to create a database specifically for you.

Get SQL Servery Management Studio onto your machine. This is a reasonable GUI that will aid you in the management of your database (s) This can be on the client machine doesn't need to be on the server.

Next learn how to create a table and investigate the difference between identity fields and primary key fields and the difference in the autonumber procedure.

Next experiment with linking to a single table from Access

Initially you may be able to just use tables that are in SQL server but still base all your forms on queries that are done from the access front end.

Next start invetstigating writing stored procedures / pass through queries etc
That's how I did it.

Note I found the best way of transferring information into SQL was actually linking the tables into an access front end and then copying the information into the SQL backend within the linked Access front end. SQL Server 2005 is quite particular about import parameters.
 
If you use the upsizing wizard, you are very likely to be able to convert the BE to SQL Server in under a half hour. This is assuming of course that your tables don't contain Access-specific data types such as multi-value fields or hyperlinks. If your app was designed with client/server techniques, everything will simply work:) If not, you'll have some work to do to take advantage of SQL Server. The whole point of upsizing is to minimize network traffic. You won't do that if your forms are bound to tables or queries with no selection criteria.

Converting to pass-through queries and stored procedures is the last thing you should do. Jet/ACE make every attempt to "pass-through" ALL queries. You can prevent that by poor techniques. The heart of Access is bound forms. Those work well with linked tables because they access few records at one time.

As Bob said, you're reading from the wrong sources.
 

Users who are viewing this thread

Back
Top Bottom