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
> Dsnless Fails At Tabeldefs.append, Access 2016    
 
   
gknoll
post Nov 20 2019, 02:12 PM
Post#1



Posts: 46
Joined: 24-September 05



OK, After searching for a day or two I fell I have to ask for help with my specific issue with using a DSNLess connection to tables on an MS SQL Server.

The code I am using I found somewhere on the net, and it works great, up until I want to use trusted connections.
It works fine when using SQL authentication, but not trusted.

The error happens at db.TableDefs.Append td and the error is a Run-time 3151.
I am a domain user in the SQL server, and I can connect to the server using SQL Management Studio using Windows Authentication.
I can also use a trusted connection from other databases that use DSN connections when I am prompted for credentials.
The driver I am using is SQL Server Native Client 11.0. I've tried others but it makes no difference.
I also have permissions to the tables I am connecting to.

Here is the function call:
CODE
AttachDSNLessTable("Contact", "dbo.Contact", "SQL1\Server", "DataBase", "")


I'd appreciate any help I can get.

Thank you
Greg

CODE
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, StServer As String, stDatabase As String, Optional stUserName As String, Optional stPassword As String)

   On Error GoTo AttachDSNLessTable_Err
    Dim td As DAO.TableDef
    Dim stConnect As String
    Dim db As DAO.Database
    
    Set db = CurrentDb()
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            db.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUserName) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & StServer & ";Database=" & stDatabase & ";Trusted_Connection=Yes" & "Persist Security Info=False;"

    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & StServer & ";Database=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword
        
    End If
    
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td

    
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Go to the top of the page
 
theDBguy
post Nov 20 2019, 02:19 PM
Post#2


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just curious, what is runtime error 3151?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gknoll
post Nov 20 2019, 02:28 PM
Post#3



Posts: 46
Joined: 24-September 05



It is a generic SQL connection error. Very non-specific.

Greg
Go to the top of the page
 
theDBguy
post Nov 20 2019, 02:51 PM
Post#4


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi Greg. How about trying to use a File DSN first and see what connection string was created for you. You can then try to use it in your code.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gknoll
post Nov 20 2019, 02:56 PM
Post#5



Posts: 46
Joined: 24-September 05



OK, I'll give that a go and let you know.

Greg
Go to the top of the page
 
theDBguy
post Nov 20 2019, 02:57 PM
Post#6


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hope it works... Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 02:58 PM
Post#7


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


Also, try using your db object variable.

So instead of:
CODE
' ...
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
' ...

try:
CODE
' ...
    Set td = db.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    db.TableDefs.Append td
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Nov 20 2019, 03:20 PM
Post#8


UtterAccess VIP
Posts: 1,879
Joined: 4-June 18
From: Somerset, UK


Try it without the Persist Security Info clause which I have never used

CODE
stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & StServer & ";Database=" & stDatabase & ";Trusted_Connection=Yes;"

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 05:24 PM
Post#9


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


If Persist Security Info is required then you have forgotten to use a semi-colon after the 'Yes' of Trusted_Connection.

Also, shouldn't it have underscores instead of spaces?

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Nov 20 2019, 06:48 PM
Post#10


UtterAccess VIP
Posts: 1,879
Joined: 4-June 18
From: Somerset, UK


According to this link at another forum it should have spaces. However as David stated there should be a semicolon beforehand.

The link also states that setting it as false is the default so it can be omitted as I had suggested

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
gemmathehusky
post Nov 25 2019, 10:12 AM
Post#11


UtterAccess VIP
Posts: 4,745
Joined: 5-June 07
From: UK


On a slightly different note, when I open a particular dbs, linked to a SQL Server backend, I relink every table.

I tried to test that the connection property for each table (tabledef.connect) was what I expected to avoid having to relink, but the SQL Server connection string is not as easy to use as ,say, the .connect property for a Jet/Ace backend. I tried to check each part of the .connect property, with what I expected to see based on the connection details, but I couldn't seem to get it right whatever I did.

In the end I have to waste some seconds reconnecting all the tables each time, just in case.

Perhaps your issue is a similar thing.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 06:08 AM