using sql server over a network (1 Viewer)

[REZ]

I have a split MS access database, I'd like to convert the backend to a SQL server on my server, and link it to frontends on other computers on the network.
I think i understand the process of exporting the tables from the MS backend to the SQL server, and I've managed to a frontend to the sQL server on the server, but how do I do it over a network?

 

[theDBguy]

Have you tried using the migration assistant (SSMA)?

 

[sonic8]

I think i understand the process of exporting the tables from the MS backend to the SQL server, and I've managed to a frontend to the sQL server on the server, but how do I do it over a network?

It should not make a difference whether the SQL Server is on the local machine or on the network.
You might need to configure the firewall on the server to allow external connections to the SQL Server though.

 

[REZ]

Do you have a code I could use to do it without a DSN?

 

[sonic8]

Not at the moment. - You should find examples when searching for "DSNless connection".

 

[Pat Hartman]

If you want to use a DSNLess connection, create a local table with a list of table names. Remember, the local table name is different from the remote server name so the two will be different.

Database   Server    ODBCTableName    LocalTableName    JetTableName    DSN
DEA_Audit            PAT-PC    dbo.tblAuditLog    tblAuditLog    tblAuditLog    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblAuditParms    tblAuditParms    tblAuditParms    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblBookmarks    tblBookmarks    tblBookmarks    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblComments    tblComments    tblComments    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblCommentText    tblCommentText    tblCommentText    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblDefinedFields    tblDefinedFields    tblDefinedFields    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblDependents    tblDependents    tblDependents    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblDivision    tblDivision    tblDivision    DEA_Audit
DEA_Audit            PAT-PC    dbo.tblDocuments    tblDocuments    tblDocuments    DEA_Audit

      '***************************************************************
      'The DoesTblExist function validates the existence of a TableDef
      'object in the current database. The result determines if an
      'object should be appended or its Connect property refreshed.
      '***************************************************************

      Function DoesTblExist(strTblName As String) As Boolean
         On Error Resume Next
         Dim db As Database, tbl As TableDef
         Set db = CurrentDb
         Set tbl = db.TableDefs(strTblName)
         If Err.Number = 3265 Then   ' Item not found.
            DoesTblExist = False
            Exit Function
         End If
         DoesTblExist = True
      End Function

Function CreateODBCLinkedTables(frm As Form) As Boolean
         On Error GoTo CreateODBCLinkedTables_Err
         Dim strTblName As String
         Dim strConn As String
         Dim db As DAO.Database
         Dim rs As DAO.Recordset
         Dim tbl As DAO.TableDef

         CreateODBCLinkedTables = False
         Set db = CurrentDb
         Set rs = db.OpenRecordset("tblODBCDataSources")
         With rs
            While Not .EOF
               DBEngine.RegisterDatabase frm.txtDSN, _
                        "SQL Server", _
                        True, _
                        "Description=" & frm.txtODBCDatabaseName & _
                        Chr(13) & "Server=" & frm.txtServer & _
                        Chr(13) & "Database=" & frm.txtODBCDatabaseName
               ' ---------------------------------------------
               ' Link table
               ' ---------------------------------------------
               strTblName = rs("LocalTableName")
                strConn = "ODBC;"
                strConn = strConn & "DSN=" & frm.txtDSN & ";"
                strConn = strConn & "Trusted_Connection=Yes;"
                strConn = strConn & "APP=Microsoft Access;"
                strConn = strConn & "DATABASE=" & frm.txtODBCDatabaseName

               If (DoesTblExist(strTblName) = False) Then
                  Set tbl = db.CreateTableDef(strTblName, _
                                dbAttachSavePWD, rs("ODBCTableName"), _
                                strConn)
                  db.TableDefs.Append tbl
               Else
                  Set tbl = db.TableDefs(strTblName)
                  tbl.Connect = strConn
                  tbl.RefreshLink
               End If

               rs.MoveNext
            Wend
         End With
         CreateODBCLinkedTables = True
         MsgBox "Refreshed ODBC Data Sources", vbInformation
         If IsLoaded("frmCheckLink") Then
            Forms!frmCheckLink!txtLinkComplete = "True"
         End If
         rs.Close
CreateODBCLinkedTables_End:
         Exit Function
CreateODBCLinkedTables_Err:
    Select Case Err.Number
        Case 3146, 3059
            Resume CreateODBCLinkedTables_End
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical, "DEA Application"
            Resume CreateODBCLinkedTables_End
            Resume
    End Select
End Function

 

[REZ]

Thank you. I'll give it a try

 

[Josef P.]

DNS less connection strings: https://www.connectionstrings.com/sql-server/

 

[Galaxiom]

Pat's code is good way to convert connections.

However the easiest way to create DSNless connections on new tables is to first create a FileDSN. Use it to link the tables and Access will configure them with a DSNless connection.

 

[KitaYama]

However the easiest way to create DSNless connections on new tables is to first create a FileDSN. Use it to link the tables and Access will configure them with a DSNless connection.

I open a connection to server and add a recordset that contains the list of all tables. Then loop through the recordset and add dsnless linked tables to FE. It takes less than 5 seconds to add more than 150 linked tables and I don't need to take care of which tables are new. Nothing's done manually and all the admin needs to do is to click a button in Admin tab of the ribbon.

OpenConnection:
    stConnect = ....................
    With Con
        .ConnectionString = stConnect
        .ConnectionTimeout = 10
        .Open
        If .State = 0 Then Exit Sub
    End With
Return


OpenRecordset:
    sql = "select * from sys.tables where type = 'U'"
    rs.CursorLocation = adUseClient
    rs.Open sql, Con, adOpenStatic
Return