Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    Unanswered: How to connect Access to diff databases

    Using File>Connection in Access one can connect to database of choice. My application needs to be able to switch from local MSDE database to server-based Sqlserver database.

    Can this be done via VBA ?

    If not, can I distribute my Access application in any (secure) way that would enable a user to do this ?
    catkins

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: How to connect Access to diff databases

    Originally posted by Colin Atkins
    Using File>Connection in Access one can connect to database of choice. My application needs to be able to switch from local MSDE database to server-based Sqlserver database.

    Can this be done via VBA ?

    If not, can I distribute my Access application in any (secure) way that would enable a user to do this ?
    Yes it can ... All that is required is to know the provider and the path and database name ... And optionally security requirements like a userID and password.

  3. #3
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58
    Yep, but I haven't got the syntax right. I tried:

    strConn = "Provider=Microsoft OLE DB Provider for Sql Server;" & _
    "Data Source=Sqldev;Initial Catalog=TestRig;" & _
    "Persist Security Info=False; ;Integrated Security=SSPI;"

    CurrentProject.Connection.Close
    CurrentProject.Connection.Open (strConn)

    and get the error:
    Operation is not allowed when the object is open

    CurrentProject.CloseConnection
    gives the error:
    6008. Microsoft Access encountered an error closing your connection
    catkins

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    ODBC

    can create a ODBC connection to the DataBASE

    then connect access to the ODBC
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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