SQL Server 2000 Stored Procedures to MS Access 2000 Queries

[CS]

hi all!

i would like to ask you guys regarding MS Access 2000 and SQL Server 2000:

Is there a possibility that I could get all of my stored procedures from SQL
Server 2000 to my MS Access 2000?

If not, are there any other alternatives as to how I could transfer my
stored procedures to MS Access (i.e. 3rd party applications that would
generate a stored procedure and convert it to MS Access queries)?

I have written a near-finished program using SQL Server when my client wants
it to be in MS Access database because it would be easier for him to deploy
the application.

I'm not as familiar doing things in MS Access, the mere thought of breaking
down my stored procedures into too many queries in MS Access would drive me
nuts! (maintenance and organization of logic data)

Please help! I would really appreciate it!

Thanks!

--A

 

[Norman Yuan]

If your client insists on using Access and the SPs you built in SQL Server
is more than simple "SELECT..." queries, I think you probably are going to
rewrite your app, at least, the Data Access Layer. If your SPs are fairly
complicated, like using CURSOR, TRIGER, TABLE VARIABLE, UDF, temporary
tables, and so on, I do not see how you can do similar things with JET at
database end. You have to move big portion of these server side computer
processes to client end - your app, therefore, rewrite your app.

 

[Albert D. Kallal]

I have written a near-finished program using SQL Server when my client wants
it to be in MS Access database because it would be easier for him to deploy
the application.

Hum, but what is the customer using for the client part of the application?
(sql server don't have forms, or anything to develop the application with?).
So, my first question would be what IDE for development of the front end UI
did you use? (ie: VB, c++ ms-access etc?).

I'm not as familiar doing things in MS Access, the mere thought of breaking
down my stored procedures into too many queries in MS Access would drive me
nuts! (maintenance and organization of logic data)

Ms-access can be used natively with sql server. On the office cd, there is a
free desktop edition of sql server that is 100% compatible with the full
blown sql server. So, you could consider using the desktop edition of sql
server, and deploy that.

It is just not clear what the client tools are that you using to build the
UI with for this database?

Remember, ms-access is not a database, but only a set of tools like VB, c++
etc to write code and build forms with. What data engine ms-access connects
to, or uses is your choice. Since you are taking about ONLY a data engine,
then ms-access is not really in question here. (however, the default data
engine is JET).

 

[Van T. Dinh]

Why don't you recommend you client to use Access Data
Project (ADP) which uses Access Front-End as the GUI but
the data resides on the SQL Server. In this case, you
should be able to use your SP with the Access Front-End.

If the client doesn't want to fork out the money for the
SQL Server license, you can use MSDE (restricted but free
version of the SQL Server) which can handle your SPs.

Grab a big Access book and read everything about ADPs.

HTH
Van T. Dinh
MVP (Access)