Post reply

Web Development - Using an MS Access Database with Linked SQL Tables

  • February 19, 2004 at 12:32 am

    #65788

    I am having trouble using an MS Access database on our website. The Database has linked tables to an SQL Server. I can open the database using access and view all of the linked data. When I attempt to access this using an ASP script I get an SQL Connection error.

    I have tried making the Web default user logon a domain user with full rights to the database to no avail.

    I have spent hours searching the net for a solution, I can find load of people with the same problem but nobody who can suggest a complete end-to-end solution.

    I know that there are ways of creating the same queries in SQL but I'm a complete SQL novice, but know Access very well.

    Any assistance would be Very Greatly Appreciated.

  • February 19, 2004 at 11:09 am

    #494756

    Try these steps:

    1. When you are creating a linked table from an ODBC DATA SOURCE MAKE SURE you cheked the SAVE PASSWORD box  

    2. then Proceed as normal with your development!

    I dont like the Idea of using obbc on top o jet oledb provider when you can just use the sqlserver provider direcly but this is just to answer your question!

     


    * Noel

  • February 19, 2004 at 11:35 pm

    #494859

    Thanks for the suggestion. Unfortunately I had already tried that and got the following error:

     Microsoft JET Database Engine error '80004005'

    ODBC--connection to 'SQL ServerServer-SQL' failed.

    Any Ideas ?

  • February 20, 2004 at 6:49 am

    #494906

    A couple things to look for:  What version is the SQL Server ODBC driver on the Web box?

    Run profiler and connect to the server that your web page is connecting to. Create a trace.  Remove all events except the Sessions and Security Audit events.  Add the Security Audit event called Audit login failed.  Then run your web app. See if there is a login failed event (if not, then your web app hasn't even tried to log in, it doesn't know where the server is).  If there is, what are the credentials that it is trying to log in as.

    Hope this helps.

    Russel Loski, MCSD

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 20, 2004 at 8:47 am

    #494943

    If you have that error then you ODBC DSN IS WRONG. Use odbc aministrator to make sure that it is connected right!

     


    * Noel

  • February 23, 2004 at 11:54 pm

    #495244

    Thanks for the suggestions so-far. The DSN is correct, I have tried a simple select statement from the SQL server using a SQL connection. This returns a recordset with no problems. The problem only occurs when MS Access is used as an intermediary.

    Any Suggestions ?

  • February 24, 2004 at 5:21 am

    #495278

    Is the ODBC a system (machine) DSN or a User DSN?  It needs to be a System DSN to work.

    Russ

    Russel Loski, MCSE Business Intelligence, Data Platform

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply