Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSDE database and access project connection proble (Access 2000, MSDE 7)

    How do I connect an Access adp to an MSDE database?

    I have an MSDE database (xyz_data.mdf, xyz_log.ldf) created on another system.
    I have installed MSDE (same version) on my development PC.
    I have copied the above two files to the same directory as the default databases have been created in (MSSQL7data).
    When I start Access, create a new adp and want to connect then the other databases appear in the list of available ones but this 'xyz' doesn't. What do I need to do to connect to the xyz database?

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: MSDE database and access project connection proble (Access 2000, MSDE

    Andy,
    you need to tell the second MSDE about the existence of the database.

    I haven't tested this myself (one of those things I haven't quite got round to!), but I believe the recommended procedure is to: detach the database from the source using sp_detach_db; copy it to the new machine; attach it to the new SQL server using sp_attach_db.

    Now, I don't know whether the target MSDE will complain about an attach if you haven't done a detach first; or indeed whether the original system will complain if you try to do something with the database if you do sp_detach it and don't reattach it again later.

    Best advice is to practise on something harmless first!

    Post back with how you get on

    Jeremy

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSDE database and access project connection proble (Access 2000, MSDE

    Thanks for the response.

    How (where) do I use the call to sp_attach_db. I can't find anything in the enterprise manager about connecting/attaching an existing database, it's all for creating new ones.

    I've never used MSDE before (nor SQL Server).

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: MSDE database and access project connection proble (Access 2000, MSDE

    Andy,

    you're in good company - I've never been let loose on a production SQL Server either - I'm currently practising on MSDE in a W98 environment as I expect/hope shortly to be involved with a SQL Server project.

    There may well be a fancy GUI way to attach/detach databases under Enterprise Manager - others can advise on this.
    Being stuck with MSDE under Win 98 where there are no GUI tools, I've got used to issuing basic commands under osql which is a rather naff command line interface that runs under a DOS prompt.

    Do you have SQL server books online? If not, then MSDN online will give you the syntax for most of the commands - including osql itself and system stored procedures like sp_detach. An example would be:

    osql -U sa
    Password:
    1> exec sp_detach_db 'mydatabasetobedetached'
    2> go
    1> exit

    I'm typing this from memory as I'm at work at present - this is the sort of thing I'd do in my Win 98 environment once MSDE is running. The -U parameter tells MSDE/Sql server the login id you're using, and then osql prompts you for a password (not displayed). The 1> and 2> prompts are generated by osql and are reset once you issue a go command to execute what you've typed previously. Typing exit at an osql prompt gets you out.

    osql /? at DOS prompt causes osql to list its command-line parameters. For system stored procedures (sp_attach etc) see sql books online or product documentation.

    It may be worth mentioning that I think there's a version of CREATE DATABASE (something like CREATE DATABASE dbname FOR ATTACH) that is also designed for creating SQL databases against existing physical files - try looking this up in an SQL reference.

    Hope this is enough to get you started. I'm not online tomorrow (Tuesday) but will hopefully get a chance to test detach and attach myself shortly.

    Jeremy

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSDE database and access project connection proble (Access 2000, MSDE

    Thanks again - I've got it connected.
    In the MSDN documentation there is an attach/detach option in the Enterprise manager but I think it is only with the full blown server, not the MSDE's version.
    I'd seen the command (sp_attach...) but not found where one entered it, the osql was new to me.
    I'm on Win 2000 so the proc you described is good for there also. Also I'd not detached the files, just copied both the mdf and the log file, I'll have to see if it has corrupted anything.

    Andy.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: MSDE database and access project connection proble (Access 2000, MSDE

    Glad it worked!

    I tested this at home yesterday and it was fine. For the record, the detach syntax is

    exec sp_detach_db 'mydatabase'

    If the physical files associated with mydatabase are mydb.mdf and mydb.ldf then you can re-attach them somewhere else (another machine or even a different folder on the same machine as a test) with:

    exec sp_attach_db @dbname = 'mynewdb', @filename1 = 'c:newlocationmydb.mdf', @filename2 = 'c:newlocationmydb.ldf'

    If you're not bothered about the log file (because MSDE will create a fresh one for you), there is also the command

    exec sp_attach_single_file_db @dbname = 'mynewdb',@physname = 'filespec of mdf data file'

    but I didn't test this.

    Jeremy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •