Changing DSN linked tables to DSNLESS tables

[AndrewDotto]

Hi Guys,

I have linked tables in my db at the moment that rely on user dsn connections to an SQL server. I've been reading about DSNLess connections and want to try convert what i have to have permanent DSNless connections, but the code i've found doesnt appear to be working. Could you give me some pointers please?

I've removed server specific details where i felt necessary, but when running the code i have it in place.

    Public Sub RefreshODBCLinks()
    
Dim connString As String
    
   
    Dim db As DAO.Database
    Dim tb As DAO.TableDef

connString = "DRIVER=SQL Server;SERVER=<database ip address>;DATABASE=<database name>;Trusted_Connection=Yes"     
 
Set db = CurrentDb

    For Each tb In db.TableDefs
    If Left(tb.Connect, 4) = "ODBC" Then
        tb.Connect = connString
        Debug.Print "Refreshed ODBC table " & tb.Name
    End If
    Next tb

    Set db = Nothing
 
End Sub

 

[RuralGuy]

Maybe you'll find this link helpful: http://www.accessmvp.com/djsteele/DSNLessLinks.html

 

[AndrewDotto]

Maybe you'll find this link helpful: http://www.accessmvp.com/djsteele/DSNLessLinks.html

Winner my friend. Thank you very much, you've made my day.

 

[RuralGuy]

You're welcome. Very glad we could help.

 

[nhorton79]

Just found this today after having some issues on a particular workstation that continually asked for connection, hence deciding to go DSN-less.

Thank you thank you thank you!

 

[RuralGuy]

You're most welcome and a *BIG* tip of the hat to Doug Steele.

 

[nhorton79]

Hi RuralGuy. I have a question. When I want to make a change to the database, i.e. add a new field to a table. Is it just as easy as going to my backend database, adding the new field and then running the fixconnections function again?
Or are there any additional steps?


Sent from my iPhone using Tapatalk

 

[isladogs]

As RG is offline, I'll answer in his place.
You are correct.
If you modify a linked table, just relink that table using the linked table manager or, if you prefer, run the fix connections procedure.

 

[nhorton79]

Thanks Ridders.


Sent from my iPhone using Tapatalk

 

[nhorton79]

One more question.
My database is normally run from inside our organisation, however I sometimes do some development from home.
Whenever I try to connect from home it can’t find the tables as the Home pc doesn’t have connection to the internal IP address of the server, but when I try to connect at work using the outside IP address it doesn’t work either, only with the internal IP.
I thought I could have a form open first and ask whether the database is being used remotely or within the work network, and then depending on whether the user clicks “Remote” or “Local” have Vba run the fixconnections module with the different IP addresses.

Sound okay? Thoughts?
Or a better way you could suggest?


Sent from my iPhone using Tapatalk

 

[isladogs]

That's the approach I have used for many years with my own databases both for home development use and so these can be used by multiple clients at different sites, each with their own connection paths.
.
I don't use Fix Connections as I have my own version but it should be possible to do exactl the same using that code.

In my case I have two tables that are used to manage relinking - often to multiple databases -using connection strings
a) tblTableLinkTypes - lists all types of connection strings for each client
B) tblTableLinks - lists all tables and the external databases of each

HTH

 

[nhorton79]

Interesting concept using fixed tables to hold the connection strings and other info.

Although I may have to skip that idea, as part of my admin area on the front end client has access to the tables, and I wouldn’t want anyone to have access to those tables and possibly see any info.

That’s just me being pedantic though, there is only myself and one other person who can see those tables.

Thanks for the info Ridders, glad to know I’m still on the right track.


Sent from my iPhone using Tapatalk

 

[isladogs]

My databases are totally locked down and clients have no access to tables.
Both the navigation pane and ribbon are hidden to clients.

All relinking is done using code run from a form

 

[nhorton79]

My clients have no access to tables from a traditional sense. I run as accde. With front end and separate backend however in the deepest depths I have a list box that has a list of tables and if you double click one it can open the table data sheet in readonly. Don’t know why, just sometimes nice to see what’s happening in the tables....


Sent from my iPhone using Tapatalk

 

[isladogs]

I distribute both ACCDB & ACCDE FE depending on the app.
In general I have 3 user security levels which I'll call standard, admin and developer for clarity. Some databases have additional levels.

The first two are used by clients.
Both have ribbon and navigation pane hidden together with other restrictions such as disabling special keys and shift bypass.
Admin users have additional menu items available but code is still not accessible to them.

Only those with developer rights can see full code, navigation pane, ribbon etc.
IMHO there is no need for anyone else to see the tables and lots of reasons why they shouldn't