VB6 Database Development - MS Access to What?

[SDJ82]

Currently we are using a Microsoft Access database and a VB6 front end

Apart from SQL Server what would be a suitable database to use to move away from Microsoft Access?

I haven't had a look at this in many years so I'm sure there are new lightweight databases out there that can be used with a VB6 front end. Our front end has over 700 Forms, Classes and Modules so a quick rewrite isn't an option

We would look to connect to the database using a DSN connection as the users that run the app store the database in different locations and not always a predefined path

[techgnome]

That depends. Does each user have their own database? Or do you connect to one single central database? It sounds like each user has their own DB, but just want to make sure.
As for DSN/DSN-less connections, I'd stick to a DSN-less connection... even if each user stores their DB in a different place... just store that location in a config file and read it when you need to and incorporate it into the connectionstring.

That said, other options besides Access would include SQLite, SLQ CE (compact edition)... there's probably others, but I'm not sure which off hand. I would probably look at SQLite.
Since you're using VB6, you may want to consider taking a look at Olaf's Rich Class 5 component ... search the forums for "olaf rc5" that should help find a thread or two where it's talked about. It has a pretty good wrapper/interface for SQLite that's fairly easy to use and takes some of the complexity out of it.

-tg

EDIT: Search for "vbRichClient5" ... that's the full name for it.

[SDJ82]

Thanks techgnome, I have been looking at RichClass5 this morning and have converted the Access database to SQLite

The database and executable tends to be stored on a server with clients running the executable via a shortcut on their machine. The database will not be stored in the same place on every installation which is why we tend to use the DSN connection as can point that to the exact location of the database

[techgnome]

Thanks techgnome, I have been looking at RichClass5 this morning and have converted the Access database to SQLite

Sweet!

The database and executable tends to be stored on a server with clients running the executable via a shortcut on their machine.

ACK! You're killing me Smalls... ugh... why? why? why? That causes a lot of network traffic with the bits being sent over the line. If it's so that updates don't need to be sent out to keep client systems up to date, then add that to your app... when it starts up, have it check a file or something on the network, if the versions don't match, drag down the latest files and then have the app update itself.

The database will not be stored in the same place on every installation which is why we tend to use the DSN connection as can point that to the exact location of the database

Sorry, I don't buy that (not even for a dollar). I haven't used a DSN in over 20 years... All you have to do is simply store the location in a config file (heck, shove it in the registry for all it matters), then simply include the location in the connectionstring when you open a connection. DSNs tend to be picky animals... and where they get created varies too... depending on if it's a 32-bit or a 64-bit process that's accessing it (I lied, I've had to deal with a DSN a couple weeks ago, and this very issue with 32/64 bit storage locations bit me in the [as you were] big time. Lost several hours on the project trying to sort that out.

-tg

[SDJ82]

ACK! You're killing me Smalls... ugh... why? why? why? That causes a lot of network traffic with the bits being sent over the line. If it's so that updates don't need to be sent out to keep client systems up to date, then add that to your app... when it starts up, have it check a file or something on the network, if the versions don't match, drag down the latest files and then have the app update itself.

That's exactly the reason, to avoid version issues, but you have provided a valid solution so I will look into this

Sorry, I don't buy that (not even for a dollar). I haven't used a DSN in over 20 years... All you have to do is simply store the location in a config file (heck, shove it in the registry for all it matters), then simply include the location in the connectionstring when you open a connection. DSNs tend to be picky animals... and where they get created varies too... depending on if it's a 32-bit or a 64-bit process that's accessing it (I lied, I've had to deal with a DSN a couple weeks ago, and this very issue with 32/64 bit storage locations bit me in the [as you were] big time. Lost several hours on the project trying to sort that out.

I had issues initially with the whole 32/64bit location for DSNs but these were ironed out very quickly and haven't been a problem since. We also have some users with a SQL Server database so the DSN allows us to connect to both types of database as required. What would be the benefit of moving to a DSN-less connection?

[SDJ82]

Sweet!

ACK! You're killing me Smalls... ugh... why? why? why? That causes a lot of network traffic with the bits being sent over the line. If it's so that updates don't need to be sent out to keep client systems up to date, then add that to your app... when it starts up, have it check a file or something on the network, if the versions don't match, drag down the latest files and then have the app update itself.

-tg

Another reason we use this setup is that we use alot of calls to App.Path for the creation of files and folders at the database location so these can be seen by all users