UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create Linked Table In Access From SQL Server Using Docmd.transferdatabase, Access 2016    
 
   
keen1598
post Nov 17 2017, 12:23 PM
Post#1



Posts: 18
Joined: 5-September 14



Hi All,

I'm trying to create a few linked tables from an SQL Server for an Access Application I have built. This code works for me as I am a credentialed user of the SQL Server. For some reason though, running this code does not use the UID and Password and instead uses a Trusted Connection, drawing on the active directory to determine who is logged into that machine. The problem with that is that not every user is credentialed on the SQL Server. Is there a way to create a DSN-less connection using a specific username and password to connect to a set of tables from an SQL Server?


CODE
Sub PASSTHROUGH()

    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database1;Trusted_Connection=No;UID = username; PWD = password", acTable, "table1", "table1"
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database2;Trusted_Connection=No;UID = username; PWD = password", acTable, "table2", "table2"
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database3;Trusted_Connection=No;UID = username; PWD = password", acTable, "table3", "table3"
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database3;Trusted_Connection=No;UID = username; PWD = password", acTable, "table4", "table4"

End Sub


Thanks all!
Go to the top of the page
 
Doug Steele
post Nov 17 2017, 01:20 PM
Post#2


UtterAccess VIP
Posts: 21,916
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Does it make any difference if you remove the spaces?

CODE
Sub PASSTHROUGH()
  
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database1;Trusted_Connection=No;UID=username;
PWD=password", acTable, "table1", "table1"
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database2;Trusted_Connection=No;UID=username;
PWD=password", acTable, "table2", "table2"
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database3;Trusted_Connection=No;UID=username;
PWD=password", acTable, "table3", "table3"
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=servername;Database=Database3;Trusted_Connection=No;UID=username;
PWD=password", acTable, "table4", "table4"
  
End Sub

(Likely won't make a difference, but no harm in trying!)

I assume your actual code replaces username and password with actual values: that you're not trying to use username and password as variables.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
keen1598
post Nov 17 2017, 01:51 PM
Post#3



Posts: 18
Joined: 5-September 14



Correct, I replace the placeholder values with actual values.

Removing spaces worked. I was able to get to the next error which was to realize that the username and password entered were not from Active Directory but from SQL Server log in data.

I can't believe it was that simple.
Go to the top of the page
 
keen1598
post Nov 17 2017, 02:46 PM
Post#4



Posts: 18
Joined: 5-September 14



New problem:

After fixing the issue with being able to create a table using SQL Server log in credentials, I tested having a user who does not exist in the SQL Server Directory, the user was unable to access the tables as the system detected the users Active Directory log in rather than the log in that was used to create the table. Once the table is created/linked, it reverts to using active directory. I tested deleting the tables, then re-running the code from that users machine, which works and doesn't require creds after the fact, but it does force you to pick unique fields....something that my end users won't know how to do.

Any ideas?
Go to the top of the page
 
Doug Steele
post Nov 17 2017, 03:05 PM
Post#5


UtterAccess VIP
Posts: 21,916
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Hmm. I'm afraid I've never encountered anything like that, so I don't have any suggestions.

Good luck!

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 10:52 AM