All,



I'm finishing up an 11 month project that uses Access with a SQL Server

back end.



I've spent a lot of time on this and other message boards dealing with

problems of late and I wanted to say a few things about what I've learned

for those out there considering this kind of solution.  I'm no expert, and

feel free to correct me if I say something that is wrong.



What I have to say is not (in any place that I can find) readily available.



First, if you are going to use Access 2000 as your front-end, USE the SQL

Server 7.0 version of MSDE as your back end.  Do not use the SQL Server

2000 version of MSDE (renamed the "SQL Desktop Engine"), unless you have

to.  Why? . . . because it will only work if your users have Office SR-

1/1a installed and depending on what your app does, they may have to have

the Access/SQL compatibility update installed, too.  This may not sound

like a big deal, but it is.  Installing both of these patches is a pain

becuase is requires your users have the Office 2K CDs during

installation . . . and you can't bundle the updates with your app . . .

and the update does not always work.  I have come across several machines

where I could not get Office SR1/1a to install without competely removing

Office, first.  In a domain environment with lots of machines, this is a

hassle . . . trust me.  For more information, you can check out these

links:



     - Microsoft Product Support Services Web Site, Incompatibility Issues

Between Access 2000 Projects and SQL Server 2000,

http://support.microsoft.com/support/kb/articles/Q269/8/24.ASP



     - Microsoft Office Web Site, Access 2000 and SQL Server 2000

Readiness Update, http://office.microsoft.com/downloads/2000/Accsql.aspx





Second . . . and a better solution I think.  If you are going to use the

SQL Server 2000 version of MSDE, then use Access XP as your front end.

Again, the major reason here is that you won't have to mess around with

the service packs.  The SQL 2000 engine does do many things that SQL 7.0

does not . . . so I recommend it.



Finally, if you are using Office Developer to package your application

with the runtime version of Access . . . if you can afford it, use the

Office XP developer addition.  Why?  For the same reasons as above.  There

are two updates for ODEV 2000 and they are somewhat painful to deal with.



. . . and on a final note . . . if you had not noticed, when packaging an

app with ODEV, the Package and Deployment Wizard does not have the nice

little radio button for "installing to All profiles" like the pure Visual

Basic 6.0 P&D wizard does.  Soooo, you'll have to provide instructions to

system administrators of WIN 2K domains explaining how to set permissions

so that Domain Users can access your application.  This can get really

ugly, especially if you are packaging any VB 6.0 designed COM .dlls with

your solution.  In a default installation of WIN 2K, domain users do not

have Read and Execute permission for msvbvm60.dll . . . so you will need

to set that one.



. . . and one more thing.  If you come from the JET world like I did, you

may find it interesting that SQL server does not support crosstab

queries.  Yep, it's true.  A good solution to this problem can be found at

Steve Dassin's "Replacement for Access Crosstab" website . . . it's free

and works wonderfully.  Here's the link:



     - http://www.angelfire.com/ny4/rac/



Hope this helps someone.



Pat Flanders



-----Original Message-----

From: Mark Irvine [mailto:markpirvine@b...]

Sent: Friday, August 24, 2001 1:10 AM

To: Access

Subject: [access] Access to SQL





Hi,



I am currently developing a database driven messaging service.  Currently I

am using Access however it is very likely that this will change to SQL

server.  My question is, when using the upsizing wizard are all of my

relationships (Enforce Referential Integrity: Cascade Delete Related

Records) be preserved or will I have to set them up again?



Mark

Having 'notes from the field' like this is one of the best things about

lists like this, IMHO--thanks for taking the time Pat.



FWIW, I'm about halfway thru the book _MS Access Developer's Guide to SQL

Server_ & am really enjoying it so far.  I don't have any active SQL Server

projects right now, but it's got a lot of good info on the differences

between Jet and SQL server, and seems like a great intro to the sort of

mind-shifts you need to go through to make good use of a server database

generally.



Cheers,



-Roy



P.S. to all--I know MS has released a Microsoft Installer-compatible

replacement for the P&D wizard in VB (the Visual Studio Installer).  Does

anybody know if there's a similar beast for Office developer?  I wonder if

you would have had any easier of a time with that...



-----Original Message-----

From: Pat and Kelley Flanders [mailto:flanders@m...]

Sent: Friday, August 24, 2001 3:56 PM

To: Access

Subject: [access] RE: Access to SQL -- An Unrelated Commentary





All,



I'm finishing up an 11 month project that uses Access with a SQL Server

back end.



I've spent a lot of time on this and other message boards dealing with

problems of late and I wanted to say a few things about what I've learned

for those out there considering this kind of solution.  I'm no expert, and

feel free to correct me if I say something that is wrong.



What I have to say is not (in any place that I can find) readily available.



First, if you are going to use Access 2000 as your front-end, USE the SQL

Server 7.0 version of MSDE as your back end.  Do not use the SQL Server

2000 version of MSDE (renamed the "SQL Desktop Engine"), unless you have

to.  Why? . . . because it will only work if your users have Office SR-

1/1a installed and depending on what your app does, they may have to have

the Access/SQL compatibility update installed, too.  This may not sound

like a big deal, but it is.  Installing both of these patches is a pain

becuase is requires your users have the Office 2K CDs during

installation . . . and you can't bundle the updates with your app . . .

and the update does not always work.  I have come across several machines

where I could not get Office SR1/1a to install without competely removing

Office, first.  In a domain environment with lots of machines, this is a

hassle . . . trust me.  For more information, you can check out these

links:



     - Microsoft Product Support Services Web Site, Incompatibility Issues

Between Access 2000 Projects and SQL Server 2000,

http://support.microsoft.com/support/kb/articles/Q269/8/24.ASP



     - Microsoft Office Web Site, Access 2000 and SQL Server 2000

Readiness Update, http://office.microsoft.com/downloads/2000/Accsql.aspx





Second . . . and a better solution I think.  If you are going to use the

SQL Server 2000 version of MSDE, then use Access XP as your front end.

Again, the major reason here is that you won't have to mess around with

the service packs.  The SQL 2000 engine does do many things that SQL 7.0

does not . . . so I recommend it.



Finally, if you are using Office Developer to package your application

with the runtime version of Access . . . if you can afford it, use the

Office XP developer addition.  Why?  For the same reasons as above.  There

are two updates for ODEV 2000 and they are somewhat painful to deal with.



. . . and on a final note . . . if you had not noticed, when packaging an

app with ODEV, the Package and Deployment Wizard does not have the nice

little radio button for "installing to All profiles" like the pure Visual

Basic 6.0 P&D wizard does.  Soooo, you'll have to provide instructions to

system administrators of WIN 2K domains explaining how to set permissions

so that Domain Users can access your application.  This can get really

ugly, especially if you are packaging any VB 6.0 designed COM .dlls with

your solution.  In a default installation of WIN 2K, domain users do not

have Read and Execute permission for msvbvm60.dll . . . so you will need

to set that one.



. . . and one more thing.  If you come from the JET world like I did, you

may find it interesting that SQL server does not support crosstab

queries.  Yep, it's true.  A good solution to this problem can be found at

Steve Dassin's "Replacement for Access Crosstab" website . . . it's free

and works wonderfully.  Here's the link:



     - http://www.angelfire.com/ny4/rac/



Hope this helps someone.



Pat Flanders



-----Original Message-----

From: Mark Irvine [mailto:markpirvine@b...]

Sent: Friday, August 24, 2001 1:10 AM

To: Access

Subject: [access] Access to SQL





Hi,



I am currently developing a database driven messaging service.  Currently I

am using Access however it is very likely that this will change to SQL

server.  My question is, when using the upsizing wizard are all of my

relationships (Enforce Referential Integrity: Cascade Delete Related

Records) be preserved or will I have to set them up again?



Mark