Re: Using Access 2007 to connect to SQL

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Ted" <tbest@xxxxxxxxxxxxxxxxx> wrote in message
news:uoeSHzPRJHA.5080@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Put database on Internet
    ... > Once you will be able to connect to MSDE or to SQL-Server, ... > and ADP in the same time as frontends to your database. ... >> will be able to serve as SQL server. ...
    (microsoft.public.access.dataaccess.pages)
  • Re: Connecting to SQL Server Express 2005
    ... understanding of SQL-Server and has nothing to do directly with DNN. ... Backup/Restore to migrate a database between two place but will use instead ... Sylvain Lafontaine, ing. ... I downloaded SQL Server Express 2005 and the SQL ...
    (microsoft.public.sqlserver.connect)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)