Virtual Server Possibility?

[rheide]

Good afternoon everyone,
I do all my work at my office and then push it to my client's servers. When I get ready to push the data, I have to change all the linked tables to reference their servers, etc.

Is there any software out there that can create a virtual server that links to a folder on your computer? For example, maybe I make a share on my computer that is actually just C:\client1server and if I use this path \\PKCMOPKS\SERV1\ it will take me there.

This way I wouldn't have to update all the places that reference their server.

I know they have software that can create virtual CD drives, etc. but I can't find anything to do what I am trying to do.

Thanks,
Randy

 

[FoFa]

Why not just use a VBA module to do the relink when needed?

 

[Travis]

I would use a DSN for linking of tables when this type of issue occurs. It keeps the changing path information from getting all screwed up.

Example code
(place in a new module)

'This code is created to link to an SQL Server. 
'Small modifications will need to be made to link to an Access db

Option Compare Database
      Option Explicit

'Constant Declaration
Private Const ODBC_ADD_DSN = 1        ' Add data source
Private Const ODBC_CONFIG_DSN = 2     ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3     ' Remove data source
Private Const vbAPINull As Long = 0&  ' NULL Pointer

'Function Declare
#If Win32 Then

    Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
    (ByVal hwndParent As Long, ByVal fRequest As Long, _
    ByVal lpszDriver As String, ByVal lpszAttributes As String) _
    As Long
#Else
    Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
    (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
    lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If

Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, pcbDescription%) As Integer
Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1

'The DSN's Name
Public Const strDSN = "RCFDATA"

'The DSN's Description
Public Const strDSNDescription = "Data Link to the RCF Database"

'The Database that you will connect to
Public Const strDatabase = "RCF"

Public Sub MakeDSN()
'Creates the DSN
      #If Win32 Then
          Dim intRet As Long
      #Else
          Dim intRet As Integer
      #End If
      Dim strDriver As String
      Dim strAttributes As String

      'Set the driver to SQL Server because it is most common.
      strDriver = "SQL Server"
      'Set the attributes delimited by null.
      'See driver documentation for a complete
      'list of supported attributes.
      
      strAttributes = "SERVER=CORPBOX01\SQLSERVER1" & Chr$(0)
      strAttributes = strAttributes & "DESCRIPTION=" & strDSNDescription & Chr$(0)
      strAttributes = strAttributes & "DSN=" & strDSN & Chr$(0)
      strAttributes = strAttributes & "DATABASE=" & strDatabase & Chr$(0)

      'Set it to use NT Security      
      strAttributes = strAttributes & "Trusted_Connection=Yes" & Chr$(0)

      'To show dialog, use Form1.Hwnd instead of vbAPINull.
      intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
      strDriver, strAttributes)
      If intRet Then
          'MsgBox "DSN Created"
      Else
          ErrMsg intRet, "DSN Create Failed"
      End If

End Sub

Public Sub RemoveDSN()
'Use this to delete the DSN 
     #If Win32 Then
          Dim intRet As Long
      #Else
          Dim intRet As Integer
      #End If
      Dim strDriver As String
      Dim strAttributes As String

      'Set the driver to SQL Server because most common.
      strDriver = "SQL Server"
      'Set the attributes delimited by null.
      'See driver documentation for a complete list of attributes.
      strAttributes = "DSN=" & strDSN & Chr$(0)
      'To show dialog, use Form1.Hwnd instead of vbAPINull.
      intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _
      strDriver, strAttributes)
      If intRet Then
          'MsgBox "DSN Deleted"
      Else
          ErrMsg intRet, "DSN Delete Failed"
      End If

End Sub

Public Function CheckForDSN() As Boolean
'Use this code to see if the DSN already exists, if it does not exist
'then call the MakeDSN procedure.
    Dim i As Integer
    Dim sDSNItem As String * 1024
    Dim sDRVItem As String * 1024
    Dim sDSN As String
    Dim sDRV As String
    Dim iDSNLen As Integer
    Dim iDRVLen As Integer
    Dim lHenv As Long         'handle to the environment
    
    CheckForDSN = False
    
        'get the DSNs
    If SQLAllocEnv(lHenv) <> -1 Then
        Do Until i <> SQL_SUCCESS
            sDSNItem = Space$(1024)
            sDRVItem = Space$(1024)
            i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024, iDSNLen, sDRVItem, 1024, iDRVLen)
            sDSN = Left$(sDSNItem, iDSNLen)
            sDRV = Left$(sDRVItem, iDRVLen)
                
            If sDSN <> Space(iDSNLen) Then
                If sDSN = strDSN Then
                    CheckForDSN = True
                    Exit Do
                End If
            End If
        Loop
    End If

End Function

Public Function AttachTables() As Boolean
On Error GoTo ErrorHandler

AttachTables = False

'You can add the tables to link to an Access Table an loop through the recordset
'Or write a DoCmd.TransferDatabase for each table to link.

DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & strDSN & ";UID=;PWD=;LANGUAGE=us_english;" _
    & "DATABASE = " & strDatabase, acTable, "tblRCF", "tblRCF"

AttachTables = True

Exit Function
ErrorHandler:
AttachTables = False
Debug.Print Err.Number & " " & Err.Description

End Function