PC Review


Reply
Thread Tools Rating: Thread Rating: 5 votes, 4.20 average.

Using Access 2007 to connect to SQL

 
 
Ted
Guest
Posts: n/a
 
      12th Nov 2008
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


 
Reply With Quote
 
 
 
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      13th Nov 2008
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
Mary Chipman [MSFT]
Guest
Posts: n/a
 
      13th Nov 2008
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.aspfa...ql-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...rDownloads.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

On Wed, 12 Nov 2008 14:28:00 -0500, "Ted" <(E-Mail Removed)>
wrote:

>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
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connect SQL server via USB give "Server does not exist or access denied" after few succeful connect. but always work wireless Vincent Microsoft Dot NET Compact Framework 4 5th Sep 2006 01:51 AM
Can connect using IP but not DNS, can also connect using DNS and other account??? Philippe Windows XP Internet Explorer 0 29th Mar 2006 01:02 AM
using a file dsn in sql.request to connect to sql server =?Utf-8?B?VHJpY2lh?= Microsoft Excel Worksheet Functions 0 20th Jul 2005 06:07 PM
How do you connect to SQL server using SQL users with Windows Auth? help =?Utf-8?B?UmV6YQ==?= Microsoft ASP .NET 2 8th Jun 2004 09:14 PM
Can't connect to SQL Server, using Windows Authentication users of SQL server? help =?Utf-8?B?UmV6YQ==?= Microsoft ASP .NET 3 7th Jun 2004 07:42 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:26 AM.