Access 2010 SQL connection string where to place it in access 2010 using DSN-Less

I need to look at using DSN-less and a connection string to connect my Access 2010 Frontend to teh SQL 2008 SQl Database.

Not sure where the best place to add it in Access 2010 and what I should use for the DSN_less string?

Need help with this.
doublexAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
0
doublexAuthor Commented:
One step ahead of you Boag2000,
I have this code in a module call CreateTableDef and trying to call the function from my autoexec macro, using runcode, calling this function:
 
AttachDSNLessTable ("stLocalTableName As String", "stRemoteTableName As String", "stServer As String", "stDatabase As String", "stUsername As String", "stPassword As String")  

but it is not finding the function in the Module, What am I missing?  Is there a better place to put it? I get this error message"
error message***********************************************************************

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
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 TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
                      stConnect = "ODBC;DRIVER=SQL Server;DALHCASQLS01V=" & stServer & ";LEAPSQL=" & stDatabase & ";Trusted_Connection=Yes"
        Else
        '//WARNING: This will save the username and the password with the linked table information.
               stConnect = "ODBC;DRIVER=SQL Server;DALHCASQLS01V=" & stServer & ";LEAPSQL=" & stDatabase & ";svc_leap=" & stUsername & ";78D6H%=" & 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
0
Jeffrey CoachmanMIS LiasonCommented:
...Not sure what "LEAP" is...?

In any event try making the function "Public" and put it in a module:

Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
...
...

Also make sure you compile the code after doing this to check for any errors
Also run the Compact/Repair Utility
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

doublexAuthor Commented:
Ok, now it see the function, but comes back with error cannot find:
error 2
I am running this from the autoexec macro in access 2010
0
doublexAuthor Commented:
The first one was for tables:
this one is the one I think I need to the SQL DB:  I am re-linking my table to this one.  I have placed it in a module also and made it public.

Is there anyway to use this with DSNLESS?
************************************************************************
'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Public Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
   
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
               stConnect = "Description=myDSN" & vbCr & "DALHCASQLS01V=" & stServer & "LEAPSQL=" & stDatabase & "Trusted_Connection=Yes"
        Else
     
    stConnect = "Description=myDSN" & vbCr & "DALHCASQLS01V=" & stServer & "LEAPSQL=" & stDatabase & "svc_leap=" & stUsername & "7jD6H%=" & stPassword
    End If
   
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
       
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
   
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
   
End Function
0
Jeffrey CoachmanMIS LiasonCommented:
Again, ...please post the info on this "LEAP" object?

That error looks like you are either not spelling the name of the server correctly when you call the function, or that the sever is unavailable, ...
So here you also need to post the code you are using to call the function (including all the arguments you are providing)
0
doublexAuthor Commented:
There is no object at the start the first thing I do in the autoexec macro is the run code.

Start with the autoexec:
Autoexec
Which call this code below:
 where I get this error msg:
Not finding  the stserver?*******************************************************************************
'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Public Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) 'As Boolean
   On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
   
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
               stConnect = "Description=DSN" & vbCr & "DALHCASQLS01V=" & stServer & vbCr & "LEAPSQL=" & stDatabase & vbCr & "Trusted_Connection=Yes"
          Else
          stConnect = "Description=DSN" & vbCr & "DALHCASQLS01V=" & stServer & vbCr & "LEAPSQL=" & stDatabase & vbCr & "svc_leap=" & stUsername & vbCr & "786H%=" & stPassword
    End If
   
    DBEngine.RegisterDatabase "DSN", "SQL Server", True, stConnect
       
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
   
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
   
End Function
0
Jeffrey CoachmanMIS LiasonCommented:
Then you are not Calling the function properly AFAICT, ...
That code is a function, and you must provide the Arguments when you "Call it"

For example:
The Macro action will still be "RunCode", but the "Function Name" will be something like this:
    CreateDSNConnection("YourServerNameHere", "YourDatabaseNameHere",YourUserNameHere", "YourPasswordHere")
0
doublexAuthor Commented:
Ok, I did that and get this error message:
new error msg
0
Jeffrey CoachmanMIS LiasonCommented:
It looks like you have trailing spaces in some of your arguments...?
0
doublexAuthor Commented:
I took them out, but still get the same error message?
0
Jeffrey CoachmanMIS LiasonCommented:
Try linking to the tables through Access directly:
External Data-->Import and Link--More...
0
doublexAuthor Commented:
I already have the tables linked through Access to the SQL server.
Just need to set tyhe connection string upon loading the frontend.

Want to set this connection with DSNless, to by pass odbc.
0
Jeffrey CoachmanMIS LiasonCommented:
Then I am at a loss here...
0
doublexAuthor Commented:
So am I, I have the Tables linked to the SQL server and talking, currently using ODBC DSn file.
But want to embed the dsn Connection string in Access startup in the Autoexec macro by calling out the function.
Just have not figured out why it will not set the connection string?
0
doublexAuthor Commented:
I downloaded the Updated SQL ODBC driver 11 and installed it and created a DSN file for it and so far our connections seem to be working much better now.

Please close this question, for now

Thanks
0
doublexAuthor Commented:
2 second  request to close this, Never got this to work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
doublexAuthor Commented:
Solution never resolved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.