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: 13
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,498
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.

--------------------
Go to the top of the page
 
keen1598
post Nov 17 2017, 01:51 PM
Post#3



Posts: 13
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: 13
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,498
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!

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 07:47 AM