Results 1 to 3 of 3
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067

    Automate Linking to SQL Server.


    I have a number of Access Databases that the data is coming from a linked SQL Server 2012 Database. I do not have DBA control over the DB. I can only link through a SQL Server Login. So everytime I open a database and try to run a query I have to login to the SQL Server Database. Is there some way to automate this?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Create a new file Data Source Name (DSN) in your ODBC Machine Data Source properties.
    As you are using 32 bit Access on a 64 bit OS, so it may need to be done under the ODBC manager directly in the SYSWOW folder in your windows system files.
    You can then link to the tables using the saved credentials in the DSN.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do a search on "dsn-less vba connection string to sql server" (no quotes).... I use the DuckDuckGo search engine.
    Here are three sites (of lots and lots) that were returned:

    http://www.freevbcode.com/ShowCode.asp?ID=1257
    http://www.accessmvp.com/DJSteele/DSNLessLinks.html
    https://accessexperts.com/blog/2011/...rosoft-access/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking tables to mdb database on extern server
    By mayja in forum Import/Export Data
    Replies: 1
    Last Post: 11-23-2016, 08:57 AM
  2. Linking to SQL Server from Access
    By JoeM in forum SQL Server
    Replies: 15
    Last Post: 05-11-2015, 09:53 AM
  3. Automate Export/Import without linking?
    By djrickel in forum Import/Export Data
    Replies: 1
    Last Post: 03-13-2014, 02:52 PM
  4. Linking access database to tables in sql server is enough?
    By masoud_sedighy in forum SQL Server
    Replies: 6
    Last Post: 01-31-2012, 07:59 AM
  5. Replies: 5
    Last Post: 03-29-2009, 07:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums