Using Access 2007 to connect to SQL

Discussion in 'Microsoft Access ADP SQL Server' started by Ted, Nov 12, 2008.

  1. Ted

    Ted Guest

    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
     
    Ted, Nov 12, 2008
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:...
    > 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, Nov 13, 2008
    #2
    1. Advertisements

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

    On Wed, 12 Nov 2008 14:28:00 -0500, "Ted" <>
    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
    >
     
    Mary Chipman [MSFT], Nov 13, 2008
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page