Server details (1 Viewer)

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
Hi,

would anyone be able to assist me on how to connect a database to a server using UNC path instead of a mapped drive?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:38
Joined
Sep 21, 2011
Messages
14,045
I thought it was just

\\servername\folder\folder ?
 

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
when you use external data to link the tables from the back end, it only allows you to go to the mapped drive then select the object, it then refers to the mapped drive. Link table manager does not allow for manual edit
how do you change to UNC path?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:38
Joined
Sep 21, 2011
Messages
14,045
You just give the path to the BE db in UNC format?
I have just copied a BE to a network location. As it is only me, it was in My Documents
I put it on my Z drive
1589797971440.png

so I used \\dlink323\usb250\downloads, and get

1589798029810.png
 
Last edited:

Minty

AWF VIP
Local time
Today, 17:38
Joined
Jul 26, 2013
Messages
10,355
What type of data source are you trying to connect to?

As @Gasman states you can simply use a direct UNC path \\MyServer\Data\ You don't need it mapped as a drive I'm certain.
 

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
my UNC path is not visable to anyone on the server, it is hidden
so I can not select it from the list
I need to be able to enter a manual path
 

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
Thank you, got it
I remembered how the IT guy set up the server location
so I could find the hidden files
 

Minty

AWF VIP
Local time
Today, 17:38
Joined
Jul 26, 2013
Messages
10,355
Depending on your datasource you can get the option to select a server or type in a UNC path ?
Hence asking what is your datasource?
 

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
another question on the UNC path
I have quite a few different databases, most interlink
all the tables must be linked into the front ends one by one
is there a way that I could have a global UNC path?
this way, if the IT manager changes servers, I can just change the global setting and not have to change every single linked table in all the different databases?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:38
Joined
Oct 29, 2018
Messages
21,358
another question on the UNC path
I have quite a few different databases, most interlink
all the tables must be linked into the front ends one by one
is there a way that I could have a global UNC path?
this way, if the IT manager changes servers, I can just change the global setting and not have to change every single linked table in all the different databases?
Hi. If you are talking using about a global variable, then you are also talking about using code to relink your tables. If so, then the answer is a yes. In fact, you can also use a table to store your UNC path and use it in your code.
 

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
Hi. If you are talking using about a global variable, then you are also talking about using code to relink your tables. If so, then the answer is a yes. In fact, you can also use a table to store your UNC path and use it in your code.
could you possibly explain to me how to do this please?
 

bastanu

AWF VIP
Local time
Today, 10:38
Joined
Apr 13, 2010
Messages
1,401
@Gizmo
Please have a look at my Fuze utility (http://forestbyte.com/ms-access-utilities/fba-fuze/), it has a table named usysFuzeTables that stores the LocalName, SourceTable, ConnectionString, TableType (Access vs ODBC). That is updated automatically but you can edit it any way you want and loop through it to refresh the links (I use a similar approach when switching from a Test to a Production environment).
The code I use to re-link:
Code:
Dim sLocalTable As String, sSourceTable As String, sDatabaseType As String, sConnect As String, sType As String
Dim tdfNew As DAO.TableDef, rstTables As DAO.Recordset


Set rstTables = appAccess.CurrentDb.OpenRecordset("usysFUZE_Tables", dbOpenDynaset)

'Lets Loop through hlpTables

Do Until rstTables.EOF

    sLocalTable = rstTables("LocalTable")
    sSourceTable = rstTables("SourceTable")
    sConnect = rstTables("ConnectionString")
    sType = rstTables("TableType")
    
    If sType <> "Local" Then
        'we have to update the connect string
        appAccess.DoCmd.DeleteObject acTable, sLocalTable
        If sType = "Access" Then
            appAccess.DoCmd.TransferDatabase acLink, "Microsoft Access", Replace(sConnect, ";DATABASE=", ""), acTable, IIf(sSourceTable = "", sLocalTable, sSourceTable), sLocalTable
        ElseIf sType = "ODBC" Then
            appAccess.DoCmd.TransferDatabase acLink, "ODBC Database", sConnect, acTable, IIf(sSourceTable = "", sLocalTable, sSourceTable), sLocalTable, False, True
        End If
        
    End If
    
rstTables.MoveNext
Loop

Cheers,
Vlad
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:38
Joined
Oct 29, 2018
Messages
21,358
could you possibly explain to me how to do this please?
Hi. There are two approaches to relinking a table. One way is to delete the table and relink it, as Vlad's code does. The other way is to modify the linked tables' Connect property and then refresh the link.
 

bastanu

AWF VIP
Local time
Today, 10:38
Joined
Apr 13, 2010
Messages
1,401
Of course, here is other way in a function, might be a better approach if all linked tables are from the same back-end as you only need to store one path in a settings table:

Code:
Public Function vcLinkTableDefs()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strNewConnectionString As String

On Error Resume Next


  Set dbs = CurrentDb()

  ' Loop through TableDefs collection, only processing
  ' the table if it already has a Connection property.
  ' (all other tables are local ... not linked)

  For Each tdf In dbs.TableDefs

    If tdf.Connect <> "" Then
    strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
        If tdf.Connect <> strNewConnectionString Then
            tdf.Connect = strNewConnectionString
            tdf.RefreshLink
        End If
    End If
  Next


End Function

In the first example I was deleting and relinking because I was replacing a local table with a linked one (similar to the built-in Access database splitter).

Cheers,
Vlad
 
Last edited:

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
Thank you
I will have to spend some time on this code
I have numerous back end files
 

bastanu

AWF VIP
Local time
Today, 10:38
Joined
Apr 13, 2010
Messages
1,401
Numerous back-ends linked to the same front-end? Then you should combine the two examples I gave you and have a local table holding each linked table local name, source table and connection string (for the Connect property that you need to update).

Cheers,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:38
Joined
Feb 28, 2001
Messages
26,999
"Numerous back end files" sounds ominous. How many is "numerous" in this context?
 

Gismo

Registered User.
Local time
Today, 19:38
Joined
Jun 12, 2017
Messages
1,298
"Numerous back end files" sounds ominous. How many is "numerous" in this context?
I have 3 back ends on different function for different departments but in the end they all tie in
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:38
Joined
Feb 28, 2001
Messages
26,999
OK, 3 is not so bad. However, you will not be able to exercise relational integrity between any two tables that are in different back-ends. Static relationships are in the BE file where they apply. Obviously, then, the relationship cannot be established to another file. (The reason is because there is no slot in the MSysRelationships table for that external link.

IF relational integrity is required, you might wish to combine the separate files.
 

Users who are viewing this thread

Top Bottom