VB with SQL Server Compact 3.5

Discussion:

(too old to reply)

Laphan

2007-12-20 11:35:30 UTC

Hi All

Just wanted to see if anybody had any experience with the following
scenarios:

1) VB6 app connecting to an SQL Server Compact Edition 3.5 DB - any
problems?

I know many machines will have .net 2+ installed by default, but I want my
app to run with the minimum of installation fuss. I'm currently using a VB6
/ Access DB combo and apart from my msflexgrid dll file to install, the
installation process is smooth. Problem is that the structure of Access DBs
can't be updated by code (correct?) so I thought it best to go up one to SQL
Server Compact and give me the flexibility to change the DB structure to
handle future updates.

2) What actual SQL code limitations are there in the Compact edition? I
heard that commands like IF and auto-number data types aren't included - is
this true?

3) What appears to be my main issue is that I work in a number of schools
and 90% of them have a Win 2003 server with Active Directory setup, whereby
the desktops have their My Docs redirected from the server's home drive and
the laptops are set for off-line file access if disconnected from the
network.

I had it that my Access DB was installed in the program files folder with my
program, but this meant that when user's log into the machine they are all
using the same data file. My plan was to put the database in the My Docs so
that each user will use the program to connect to their database in their My
Docs folder - do you envisage any problems with this setup?

I really don't want to go the Express route due to the installation hassle
for users and bloated installation package.

Many thanks for any advice you can give.

Rgds

Ginny Caughey [MVP]

2007-12-20 12:10:20 UTC

Permalink

Laphan,

I doubt that SQL Compact has ever been tested with VB6 - that's a pretty old
language. Go ahead and use VB.NET. Machines that don't have .NET installed
can get it from Windows Update.

There are several reasons not to put your app's data under the Program Files
tree. In Vista that would create User Account Control issues. In your
scenario where that would resolve to a network location, it just won't work.
SQL Compact is not designed for multiuser use, and this may be the most
important point in your decision whether SQL Compact is appropriate for your
situation or not.

There are a number of important differences between the SQL language
supported on SQL Compact and Express. Your best bet would be to check Books
on Line for SQL Compact to see what is available:
http://www.microsoft.com/downloads/details.aspx?FamilyID=1FF0529A-EB1F-4044-B4B7-40B00710F7B7&displaylang=en
and here's a link to the SQL Reference for 3.5 on MSDN:
http://msdn2.microsoft.com/en-us/library/ms173372(SQL.100).aspx As you can
see the timestamp data type is supported.
--
Ginny Caughey
Device Application Development MVP

Post by Laphan
Hi All
Just wanted to see if anybody had any experience with the following
1) VB6 app connecting to an SQL Server Compact Edition 3.5 DB - any
problems?
I know many machines will have .net 2+ installed by default, but I want my
app to run with the minimum of installation fuss. I'm currently using a VB6
/ Access DB combo and apart from my msflexgrid dll file to install, the
installation process is smooth. Problem is that the structure of Access DBs
can't be updated by code (correct?) so I thought it best to go up one to SQL
Server Compact and give me the flexibility to change the DB structure to
handle future updates.
2) What actual SQL code limitations are there in the Compact edition? I
heard that commands like IF and auto-number data types aren't included - is
this true?
3) What appears to be my main issue is that I work in a number of schools
and 90% of them have a Win 2003 server with Active Directory setup, whereby
the desktops have their My Docs redirected from the server's home drive and
the laptops are set for off-line file access if disconnected from the
network.
I had it that my Access DB was installed in the program files folder with my
program, but this meant that when user's log into the machine they are all
using the same data file. My plan was to put the database in the My Docs so
that each user will use the program to connect to their database in their My
Docs folder - do you envisage any problems with this setup?
I really don't want to go the Express route due to the installation hassle
for users and bloated installation package.
Many thanks for any advice you can give.
Rgds

Laphan

2007-12-20 12:26:52 UTC

Permalink

Hi Ginny

Many thanks for the prompt reply.

Just to delve deeper, I don't want multiple users to access the same data -
in fact I want to do the complete opposite.

To explain the current issue:

1) App and ms access db installed into program files on a desktop machine.

2) Teacher A logs into the machine using her AD login and password.

3) Teacher A runs up my app and enters data into it, which is stored in the
access db.

4) Teacher A finishes her work and logs off the machine.

5) Teacher B logs onto the machine using her AD login and password.

6) Teacher B runs up my app and finds that somebody else's data is already
in this db. Teacher B will probably do the first thing that comes in their
head and restore over the top of the db with a backup of theirs. Teacher B
can now continue and Teacher A has nowt!

My plan was to change it as follows:

1) App and template db (sql compact) installed into program files on a
desktop machine.

2) Teacher A logs into machine.

3) Teacher A runs up app, app checks if their My Docs folder contains a data
file (in a specific place), if exists (ie teacher has used the app before)
then continue as normal, if doesn't exist then app copies template db to my
docs folder and continues as normal.

4) Teacher A is now using my app, which is connected to the database in
their My Docs folder (which is a folder re-direct from the server).

5) Teacher A finishes her work and logs off the machine.

6) Teacher B logs into machine.

7) Teacher B runs up app, app checks if their My Docs...... etc

8) Teacher B is now using my app.......

and so on.

Is this a possibility?

Surely this is something like how MS Outlook kind of works with it's PST
file for each user on the machine??

Rgds

"Ginny Caughey [MVP]" <***@wasteworks.com> wrote in message news:4866E9A5-2B25-4A74-A18E-***@microsoft.com...
Laphan,

I doubt that SQL Compact has ever been tested with VB6 - that's a pretty old
language. Go ahead and use VB.NET. Machines that don't have .NET installed
can get it from Windows Update.

There are several reasons not to put your app's data under the Program Files
tree. In Vista that would create User Account Control issues. In your
scenario where that would resolve to a network location, it just won't work.
SQL Compact is not designed for multiuser use, and this may be the most
important point in your decision whether SQL Compact is appropriate for your
situation or not.

There are a number of important differences between the SQL language
supported on SQL Compact and Express. Your best bet would be to check Books
on Line for SQL Compact to see what is available:
http://www.microsoft.com/downloads/details.aspx?FamilyID=1FF0529A-EB1F-4044-B4B7-40B00710F7B7&displaylang=en
and here's a link to the SQL Reference for 3.5 on MSDN:
http://msdn2.microsoft.com/en-us/library/ms173372(SQL.100).aspx As you can
see the timestamp data type is supported.
--
Ginny Caughey
Device Application Development MVP

Ginny Caughey [MVP]

2007-12-20 12:48:22 UTC

Permalink

Lapham,

It sounds like your scenario would work, but since I haven't tried that you
might want to do a small test before commiting to that approach.
--
Ginny Caughey
Device Application Development MVP

Post by Laphan
Hi Ginny
Many thanks for the prompt reply.
Just to delve deeper, I don't want multiple users to access the same data -
in fact I want to do the complete opposite.
1) App and ms access db installed into program files on a desktop machine.
2) Teacher A logs into the machine using her AD login and password.
3) Teacher A runs up my app and enters data into it, which is stored in the
access db.
4) Teacher A finishes her work and logs off the machine.
5) Teacher B logs onto the machine using her AD login and password.
6) Teacher B runs up my app and finds that somebody else's data is already
in this db. Teacher B will probably do the first thing that comes in their
head and restore over the top of the db with a backup of theirs. Teacher B
can now continue and Teacher A has nowt!
1) App and template db (sql compact) installed into program files on a
desktop machine.
2) Teacher A logs into machine.
3) Teacher A runs up app, app checks if their My Docs folder contains a data
file (in a specific place), if exists (ie teacher has used the app before)
then continue as normal, if doesn't exist then app copies template db to my
docs folder and continues as normal.
4) Teacher A is now using my app, which is connected to the database in
their My Docs folder (which is a folder re-direct from the server).
5) Teacher A finishes her work and logs off the machine.
6) Teacher B logs into machine.
7) Teacher B runs up app, app checks if their My Docs...... etc
8) Teacher B is now using my app.......
and so on.
Is this a possibility?
Surely this is something like how MS Outlook kind of works with it's PST
file for each user on the machine??
Rgds
Laphan,
I doubt that SQL Compact has ever been tested with VB6 - that's a pretty old
language. Go ahead and use VB.NET. Machines that don't have .NET installed
can get it from Windows Update.
There are several reasons not to put your app's data under the Program Files
tree. In Vista that would create User Account Control issues. In your
scenario where that would resolve to a network location, it just won't work.
SQL Compact is not designed for multiuser use, and this may be the most
important point in your decision whether SQL Compact is appropriate for your
situation or not.
There are a number of important differences between the SQL language
supported on SQL Compact and Express. Your best bet would be to check Books
http://www.microsoft.com/downloads/details.aspx?FamilyID=1FF0529A-EB1F-4044-B4B7-40B00710F7B7&displaylang=en
http://msdn2.microsoft.com/en-us/library/ms173372(SQL.100).aspx As you can
see the timestamp data type is supported.
--
Ginny Caughey
Device Application Development MVP

Norman Yuan

2007-12-20 14:40:11 UTC

Permalink

Multiple users logging on to the same computer using the same data file
isn't a pretty solution, be it Jet DB (Access DB) or SQL Server CE or
something else.

Following the way WIndows storing user data, I would:

1. Install the APP and a data template (say, a well-designed, empty Jet
database *.mdb/*.mde/*.accdb...) in C:\Program Files\MyApp" folder.
2. WHenever a user starts the app, check his default data folder
(C:\Documents and Settings\username\Application Data\MyApp\") to see if
there is the required data file/database file. If not, copy the template to
there and then go on.

Since every user logs on to a Windows computer will have his own profile
folder created by WIndows automatically, each user's data will not be mixed
to othewrs, they will not able to see other's data unless the user is an
admin (local or domain).

BTW, you can modify Jet DB (Access DB)'s structure through VB code, by using
either DAO, ADO/ADO Ext.

In your case, SQL Server CE or Access DB both are suitable to use. I just do
not like the idea if every user has to erase other's data each time, and do
not like the idea of placing data in ...Program Files (in fact, you may run
into permission issue: XP and Vista do not give user write-permission to
...Program Files by default).