Post reply

Where and How to find the Computer_Name for Data Source of SQL Server Database?

  • September 27, 2005 at 4:19 pm

    #593495

    I'm struggling.

    I tried connecting to MSDE with Access (I have version XP/2002) on my test box using both a data project (.adp) and an ODBC connection in a MS Access (.mdb) database. In both cases connections to the default instance and a named instance worked fine using integrated security - The instances are not configured for mixed mode. I don't have MSDE release A, but would expect it to work too.

    Here are a few ideas:

    If I remember correctly MSDE is delivered short of tools. Microsoft offer a web based manager (SQL Server Web Data Administrator); the download link is

     http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en

    I've not used it, so I can't vouch for it. From the short description on the download page, I assume it's equivalent to Enterprise Manager, but probably doesn't have all the features.

     

    Try defining an ODBC connection [Start -> Control Panel -> Administrative tools -> Data Sources (ODBC)].

    Select the SQL Server driver (it's the last in the list). Click Finish.

    On the next screen, fill in a name and a description. You should be able to select your instances in the Server drop-down list. Click Next.

    Select your login options - either windows authentication (=Integrated Security) or SQL/Server authentication using a userid (sa?) and password. Click next.

    Tick the checkbox labelled "Change the default database to:" You should see a list of databases you can access. If databases you think should be there aren't listed, that's because you don't have permissions to the database.

    Select a database (NorthwindCS). Click Next, then Finish.

    You should see a confirmation page. Click the button labelled "Test Data Source".

    If all goes well, you should see the test results page with the text "TESTS COMPLETED SUCCESSFULLY!"

    Click OK three times.

    If that worked, you should be able to use the connection you just defined in a .mdb (=Blank database in A2002) to connect to your database using [File -> Get External Data -> Link Tables], selecting ODBC connection.

    Reading back through the posts, I saw my connect string for a SQL/Server authenticated connection to the named instance was incorrect; It should be

    Provider=SQLOLEDB;Data Source=(local)\Access2003;Initial Catalog=NorthwindCS;UserId=sa;Password=<yourNewSQLServerPassword>;

    Hope this helps

    Regards

    Otto

     


    Best Regards,

    Otto Schreibke

    The future is a foreign country, they do things differently there.
    (Stolen from Arthur C Clarke)

Viewing post 16 (of 15 total)

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