Using Access 2007 to connect to SQL

[Ted]

Hi all,

I'm a total newbie w MS SQL Server. We're running SQL 2005 here w a new
system. I need to pull data from SQL into my Access 2007 Policy Issuance
system. Eventually I'll migrate this data to SQL too but for now its being
stored in a backend Access database. We have a contractor working in the
back-end of our new sytem. She's setting it up so that our users will enter
a quote ID from the new system pull in the data. I need to write code to
connect to the SQL Server to pull in the data associated with that Quote ID.
Can anyone recommend some sample code that can get me rolling? Or any sites?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
She offered me kind of an outline to get going...
1. declare and/or initiate some variables:

a.. one for the server name
b.. one for the database name ('CIS')
c.. one for the userid
d.. one for the password
e.. one for an error code from MSSQL (code not yet written - i haven't
quite gotten to error trapping yet - don't let me forget)
f.. one for each SQL Command you want to pass to the server which will
turn out to be one command per table, I believe a total of 8
(the first of these commands is the text contained in the file
spRCA_tblSNMain_Execute_11-07-2008.txt - you insert a quoteid variable where
I have a quoteid static value)
g.. one for each parameter you will need to pass along and/or receive,
including the quote number that the user inputs that u will use as described
above in the SQL Command
2. Connect to server w/ userid & password
3. select a database ('CIS')
4. pass the sql command(s) with the quote number variable embedded
5. insert the return values or datasets into access
6. store the access quoteid and other internal key values in a variable to
use for the next insert statement for the next table
7. perform any other stuff we come up with such as error trapping
8. close connections

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Any help would be very much appreciated
Ted

 

[Sylvain Lafontaine]

Hi, this newsgroup is about ADP and SQL-Server and has nothing to do with
MDB (or ACCDB) database file format, ODBC linked tables, passthrough queries
and DAO.

For your case, I would suggest that you begin with taking a look at ODBC
linked tables; probably the easiest way of retrieving data from a SQL-Server
to a MDB/ACCDB database. If you want to retrieve data using a stored
procedure on SQL-Server, you will have to use a passthrough query instead.
You should post this to a newsgroup such as
microsoft.public.access.odbcclientsvr or m.p.a.externaldata.

Also, without beeing rude at all, a quick look at your elements a. to g. and
1. to 8. show that you don't seem to have any familiarity at all with using
Access as a frontend against SQL-Server; so I would suggest that you first
begin by reading a good book on the subject such as "Microsoft Access
Developer's Guide to SQL Server" from Mary Chipman and Andy Baron or "Access
2000 Client/Server Solutions" from Lars M. Klander. Also, a lot of good
books on Access have chapters that deals with the process of working with
Access against SQL-Server.

 

[Mary Chipman [MSFT]]

Yes, there's quite a bit of code out there that can help you get
going, but the learning curve is high if you're not familiar with SQL
Server. This whitepaper, Optimizing Microsoft Office Access
Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx gives you the
background information on the differences between the two engines.
Here's another one: What are the main differences between Access and
SQL Server?
http://sqlserver2000.databases.aspf...ifferences-between-access-and-sql-server.html.
Armen Stein (Access MVP) has another great resources on his site: "The
Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp and
Luke Chung of FMS has papers and other resources on his site: FMS
Upsizing Center http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx.
The Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446 is full of code that is still
applicable and fortunately still in print. Hopefully these will get
you started, and don't hesitate to post back if you have any further
questions.

--Mary