[AccessD] Back End Bloat - Revisited

Hi Don

Piece of cake. Only trap when installing SQL Express is that you later have to use the SQL Manager Express to adjust the db engine to allow connections from outside as this are blocked by default. And you will probably have to set your db to both Windows authentication as well as native authentication (log in) or just native alone.

Access has an upsize wizard which does a pretty good job copying all your backend tables to a new db in SQL Server and creating new links.
Some queries in code may need to have the option dbSeeChanges specified.
Then you can access the SQL Server via ODBC which is OK for the amount of data and traffic volume you have mentioned.

Further, Susan and Martin have written a splendid book on this exact topic, "SQL: Access to SQL Server":

  http://www.amazon.com/SQL-Access-Susan-Sales-Harkins/dp/1893115305

Not that I have it, but I guess it is aimed at people like you in your situation.

Finally, don't forget our SQL Server dedicated lists where a handful or two of experts (I'm not one of those) will help you.

/gustav


>>> Donald.A.McGillivray at sprint.com 02-01-2009 06:47 >>>
Dunno, John.

I suppose that our IT overlords would prefer that we be using something other than Access.  SQL server is on the list of approved alternatives, but I don't know about Express.

Will that integrate pretty seamlessly with my Access front end?  What's the learning curve like for somebody whose experience with databases is pretty much limited to working with Access?

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, January 01, 2009 7:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Back End Bloat - Revisited

Any chance you could just move this BE to SQL Server Express?

John W. Colby
www.ColbyConsulting.com