DNS-less connection between MS Access and SQL

Hello,

Thanks in advance for any help someone may provide.  I am creating a front end ms access application with a back end sql db.  I would like to create a link with a DNS-less connection and need some help.

I have an article I found from Microsoft... here is the link: https://support.microsoft.com/en-us/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in

So far I created the module and now I am using a form as described... invoking the module on the open form... but since this is the first time I am working with modules, not sure how to proceed.  It seems to me that I have provide some parameters and not sure how to do this.

Can anyone help please.  Thanks again.

Joy Gomez
Joy GomezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Gustav BrockCIOCommented:
Well, without your code, what can we do? And it is DSN, not DNS.

/gustav
0
Kelvin SparksCommented:
I use DSN-less connection always. You start but creating a local table this lists every table you want to connect - with its sql Server name and the name you'd like to use in Access (e.g. dbo.MyTable and MyTable).

I then have a function in a module that loops these tables and connects them.

Code is like

Public Function RelinkSQLTables()
On Error GoTo EH

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strTblServer As String
Dim strTblLocal As String
Dim rsTables As DAO.Recordset
Dim sSQL As String
Dim strConnect As String

Set db = CurrentDb()

sSQL = "SELECT * FROM ODBCTableNames where Not IsNull(ODBCName);"

Set rsTables = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rsTables.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rsTables!LocalTableName Then
            ''Build the dsn-less connection string
            db.TableDefs.Delete rsTables!LocalTableName
            Exit For
        End If
    Next
        strConnect = "ODBC;Driver={SQL Server};Server=" & rsTables!ServerName & ";Database=" & rsTables!DatabaseName & ";Trusted_Connection=True"
        Debug.Print strConnect
        Set tdf = db.CreateTableDef(rsTables!LocalTableName, dbAttachSavePWD, rsTables!ODBCName, strConnect)
        db.TableDefs.Append tdf
    rsTables.MoveNext
Loop

End Function

You could make this more robust by checking for a tables existence, and removing it, first, so that you could pick up table changes. Note that I also include the server and database names in my local table.

Kelvin
0
Joy GomezAuthor Commented:
Hi Kelvin,

Thanks for the code... however can you guide me a bit through this... i am a starter vba coder... This is what I have done so far.

1. Created a table in access called  "ODBCTableNames".  
2. This has 2 columns called "Name" (for sql table name dbo.MyTable) and "LocalTableName" (for access table name MyTable)

First question I have is... what do I need to change in this code based on the table/column names I created.
Second question I have is... can I call this from a form on the event for open form.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Joy GomezAuthor Commented:
Kelvin... correction above...
The column names are "SQLTableName" and "LocalTableName"
0
Kelvin SparksCommented:
Add Columns named ServerName and DatabaseName, edit the column named Name to be ODBCName, Populate the new columns.
I tend to run it once from the module window, as this doesn't change often. I do it in the "master " copy of my tool, then distribute. Any changes will only happen with the changes that are made to the database design.

One issue I forgot - what type of sql server authentication are you using? I'll need to give you a public constant that provides the ODBC connect.

Kelvin
0
Joy GomezAuthor Commented:
Ok I did that... I am going to use windows authentication.
0
Joy GomezAuthor Commented:
Kelvin.. I am also getting this error... as attached in the image...
0
Kelvin SparksCommented:
No image??
0
Joy GomezAuthor Commented:
wait.. it worked.!!!  you are a God!
0
Joy GomezAuthor Commented:
I ran that from the module itself.  Now lets say i want to run it from a open form... how do i do that?
0
Kelvin SparksCommented:
Create a button, or use a form open event to call the function.

Code would be
Call RelinkSQLTables
0
Joy GomezAuthor Commented:
Now I am getting this error... Attached image...
0
Joy GomezAuthor Commented:
sorry... here is the image...
Picture2.png
0
Kelvin SparksCommented:
Odd, what did you name the module when you saved it, what is the function name. They can't share the same name. Check that you have actually saved the module where the code was pasted.

Kelvin
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
Joy GomezAuthor Commented:
Yeah that is what I did... named them the same... just changed it and it started to work...

Kelvin... thank you so much for your quick response... you really made a difficult process quite easy for me... have a good day.
0
Joy GomezAuthor Commented:
This is the first time I am using Experts Exchange... and Kelvin truly made it easy for me to get a solution. He turned what was a difficult issue to tackle and made it extremely easy for me to do. Thank you.
0
Kelvin SparksCommented:
It was a pleasure, I remember starting off with Access in the 1990's and having to find solutions for what seemed big problems (good textbooks were the key in those days).

Enjoy you're learning curve.

Kelvin
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
SQL

From novice to tech pro — start learning today.