Another Newbie Question (1 Viewer)

[JimmieC]

I attempting to make the change to a sql server 2005 express be linked to an Access2003 fe. I have created the tables in sql server 2005 and linked to them in Access through ODBC. My question is can I create a stored procedure in access or can I copy a select access query and paste it to sql server and save it as a stored procedure? So far I haven't found a gui interface in sql server to design and save a stored procedure. If I am able to create a stored procedure in sql server then how do I access it in my Acess fe? Sorry for the beginner questions but I'm completely self-taught and have to grind out every single concept.

Thanks

Jim

 

[tehNellie]

You can create a Project (.adp) file in Access which basically treats Access as a front end to SQL server. This will let you create Views and Stored procedures from within Access directly on the SQL server rather than using linked tables.

If you want to continue using access "stand alone" and link to SQL server (nothing wrong with that, I use several databases in this configuration) you can execute Stored procedures on the SQL server, but you'll have to do it via VBA.

I've not used SQL server 2005 express edition or otherwise, but there isn't a similar tool to the view gui for Stored procedures, although there is nothing to stop you designing your stored procedure using this tool then finishing it off using the Query Analyser.

 

[JimmieC]

Thanks

tehNellie,
Thanks for taking the time to respond. The database I am working on is used by approx. 4 computers and 3500 records per year will be added. It has approx. 20 tables and 175 queries, many of the queries are nested. Also, the database has 100 reports many with subreports. Many of queries use criteria supplied by forms. Considering this database, would you choose mdb linked to sql or an access adp. I'm comfortable with Access but have not used SQl Server and so far I don't know where to start. I like the idea of an ADP but I read comments from those who advise to not use ADP but rather link with mdb. Thanks for any comments or advice.

Jim

 

[tehNellie]

Certainly in the short term I would probably go with Linked Tables/Views as it will take less time to convert your existing Access application to run off SQL server.

What you might want to consider though is to start making some modifications to how your database works to improve performance. Can you move some of your SELECT Queries to Views or Pass-Through Queries to take advantage of the performance benefits that SQL server potentially offers and possibly make your network admins a bit happier. By default, as I understand it, if you run a query on two linked tables, Access will pull all the data down locally, perform the query and then display the results, which with large tables means you could be moving lots of data around your network. If you run a passthrough query or link to a View then you'll only be pulling the information that you actually want in the first place over the network. And SQL server is much faster at querying data than JET.

While you can just run an INSERT or UPDATE query on a linked table, you might also want to consider looking at calling stored procedures from within your VB code. From a SQL server perspective I hate applications that send "raw" SQL to my database, they make planning changes on the back end database very difficult as you have no idea what effect it might have on the front end application. I'll spare you the sob story of how I inherited a SQL server DB [badly] designed by a web Developer with no rules or relationships on any of the tables "Because it's all handled by my webcode [Which I didn't document]" using no Stored Procedures or views whatsoever. (OK, I guess I wont )

 

[JimmieC]

Hello tehNellie,

Thanks for the response. I actually had an almost finished database using adp with sql server 2000 and then upgraded to sql server 2005 express. This turned out to be a mistake as I now cannot create sql server objects in Access. So, I'm learning to use the SQL Express Manager with an both an .mdb and an .adp file and hoping the learning curve is not tooooo steep. I appreciate your good advice.

Cheers,

Jim

 

[wazz]

i thought i read somewhere that you cannot create stored procedures with the SQL express edition. (is that what you meant by, "I now cannot create sql server objects in Access." ?)

 

[JimmieC]

Yep, that's what I meant. Maybe Access 2007 will bring me back to the place I had with Access 2002 and SQL Server 2000.

Cheers,

Jim