Server details (1 Viewer)

Gismo

Registered User.
Local time
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
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, 23:38
Joined
Sep 21, 2011
Messages
5,671
I thought it was just

\\servername\folder\folder ?
 

Gismo

Registered User.
Local time
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
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, 23:38
Joined
Sep 21, 2011
Messages
5,671
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, 23:38
Joined
Jul 26, 2013
Messages
6,957
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
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
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
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
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, 23:38
Joined
Jul 26, 2013
Messages
6,957
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
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
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
Local time
Today, 15:38
Joined
Oct 29, 2018
Messages
10,005
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
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
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

Registered User.
Local time
Today, 15:38
Joined
Apr 13, 2010
Messages
372
@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
Local time
Today, 15:38
Joined
Oct 29, 2018
Messages
10,005
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

Registered User.
Local time
Today, 15:38
Joined
Apr 13, 2010
Messages
372
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
Tomorrow, 01:38
Joined
Jun 12, 2017
Messages
418
Thank you
I will have to spend some time on this code
I have numerous back end files
 

bastanu

Registered User.
Local time
Today, 15:38
Joined
Apr 13, 2010
Messages
372
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

Happy Retired Curmudgeon
Local time
Today, 17:38
Joined
Feb 28, 2001
Messages
16,760
"Numerous back end files" sounds ominous. How many is "numerous" in this context?
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 17:38
Joined
Feb 28, 2001
Messages
16,760
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 (Users: 0, Guests: 1)

Top Bottom