Converting Access to standalone

[orbic1]

Does anyone know anything about converting access to a standalone app such as Java, or VB with an SQL backend? At somepoint I'll need to upgrade it to SQL, and a VB front end would be easier!

More to the point, as I know something like this does exist, is anything any good? Is it worth doing, and how easy is it to upgrade the DB to an SQL backend?

Cheers!

 

[Kodo]

I've upgraded Access to SQL server several times. There's always a few things you have to make sure you check. For instance: Date variables being passed in access are surrounded with # where in SQL server they are not. ex: #"&thedate&"# in access would be '"&thedate&"' in SQL server.

The transfers are relatively painless as far as the database itself goes.

I'm not sure about updating the modules to a VB6 app though. I've only had to deal with it using an ASP application I wrote. The total time for that including testing was a few days give or take a few extra hours for straggling errors. I also spent some time to re-write some code and combined some existing queries into more robust stored procedures.

 

[orbic1]

Thanks

Thanks for that - very valuable info! Just a couple of questions (I am a complete newbie to SQL databases, particularly upgrading from Access, however a little exp w/MySQL and PHP):

Is it worth upgrading?
I presume I'd have to run Win Server + SQL Server
Each machine would just use an access (or whatever) frontend?

Thanks

 

[Kodo]

there many options. Some better than others.

On the MS Office CD's there is a folder called MSDE. It is a workstation version of SQL server. It stands for Microsoft Data Engine. You can install that (make sure you read the install instructions for the msde as it is IMPERITIVE to it's functioning) to use on your desktop or for an intranet. As usual, it's a slightly crippled version of the big-pappa SQL server.

You don't need Win Server to run SQL server or the MSDE BUT you should have plenty of RAM.

Ideally, You would have Win Server plus SQL server but it is not an absolute necessity and that, of course, it highly dependent on your situation; i.e. if you're devl'ing and testing or in production etc.

If you go with SQL server, you have the Enterprise Manager to use to administer the server. If you go with the MSDE (free by the way) you can use something like SQLExecMS http://www.laplas-soft.com/ to administer it. There are command line instructions as well, but they are a PITA.

You can then create any front you want. An access project or a VB, .NET, ASP3 ..anything.

It is VERY worth updating from access backend to at least the MSDE. It's much faster and you can save yourself some application coding by shoving the logic into a stored procedure. For example (basic). In access, I had to use a bunch of different queries to run counts. In SQL server, I made one SPROC with multiple sub selects for all the counts and returned all the values. One hit to the db connection instead of 10. You do the math.

 

[Kodo]

Pat, thanks for that info. I've never upgraded from a VBA app in access to using SQL server. Mostly I've just updated the schema and the queries etc. Reason being I'm primarily an web devl.
Good info! thanks again.

 

[orbic1]

Thanks!

Thank you both for that! I'll explore each of these very soon! Very useful info. The primary reason I would need to upgrade is multiple users accessing the same data accross multiple sites with forms and reports that need to make multpile searches (workstations wont always be particularly state-of-the-art!). Is there a better way of doing this than upgrading? Or what is the best way of upgrading?

Cheers