Access Development Extensions (1 Viewer)

[Phil_b]

I am need of urgent help with the following situation:

Currently my project has been developed on my computer with frontend Access, backend SQL Server (on our companys server). Now that I am testing release as soon as I create the package and install it on any other computer it says that it cannot connect to the SQL Server...

Is there something I should add (macro) or otherwise to make sure that it recognises the SQL server and connects?

As a point of note the release will be on the RunTime engine rather than full access.

Thanks for any help.

Phil

 

[Phil_b]

any help with this?

If not is there a more appropriate place i should be asking this question? It is becoming increasingly urgent currently as I need to distribute the Access frontend ASAP.

Phil.

 

[FlyerMike]

How are you linking to the SQL Server tables?

Do you authenticate the user and then link to the tables? Is it possible that the test users don't have proper privileges to the SQL server?

 

[Phil_b]

currently its via authentication although I use the package wizard to transport the database to the other computers.

This means they are all using RunTime although what I think is happening is the DB details and connection specific entries are not being added to the 'package'. It literally says that it cannot find an SQL server on the computer (although it should be looking for an external SQL server).

Hope that gives abit more insight into this.

Phil

 

[FlyerMike]

I'm not familiar with the package wizard.

When I distribute an application, the app checks for a list of linked tables. If a table is not linked, the app is directed to a server location and given the table name and password to link to the table.

The details of the link changes for an SQL table, but the following ought to make some sense... I hope.

Public Sub LoadTables()
On Error GoTo Err_LoadTables

    Dim sResult As String
    Dim MyRS As DAO.Recordset
    Dim sTableName As String, sDataPW As String, sSecPW As String, sPurchServPW As String, sConnect As String
    Dim sDatabaseName As String, sDirectory As String
    Dim sStatus As String
    'Open list of tables to load
    sDataPW = "pwd1"
    
    sResult = "SELECT tbl_Tables_to_Load.* FROM tbl_Tables_to_Load;"
    
    Set MyRS = CurrentDb.OpenRecordset(sResult)
    
    With MyRS
        If .RecordCount Then
            .MoveFirst
            While Not (.EOF)
                sDirectory = Nz(.Fields("File Location").Value, "")
                If GRUNLOCAL Then
                    sDatabaseName = LOCAL_DIR
                ElseIf VBA.Len(sDirectory) Then
                    sDatabaseName = .Fields("File Location").Value
                Else
                    sDatabaseName = NETWORK_DIR
                End If
                If VBA.Right(sDatabaseName, 1) <> "\" Then
                    sDatabaseName = sDatabaseName & "\"
                End If
                If Not (IsNull(.Fields("File Name").Value)) Then
                    sDatabaseName = sDatabaseName & .Fields("File Name").Value
                    If Not (IsNull(.Fields("Table Name").Value)) Then
                        sTableName = .Fields("Table Name").Value
                        if Not(IsConnected(sTableName))
                            sConnect = ";DATABASE=" & sDatabaseName & ";pwd=" & sDataPW
                            Call ConnectTable(sTableName, sConnect, sTableName)
                        End If
                    End If
                End If
                .MoveNext
            Wend
        End If
        .Close
    End With

Exit_LoadTables:
    Set MyRS = Nothing
    Exit Sub
    
Err_LoadTables:
    Call ErrHandler("LoadTables routine", Err.Number, Err.Description)
    Resume Exit_LoadTables

End Sub

Public Function IsConnected(sTableName As String) As Boolean
On Error GoTo Err_IsConnected

    Dim bResult As Boolean
    Dim tdTemp As TableDef
    
    bResult = False
    
    Set tdTemp = CurrentDb.TableDefs(sTableName)
    
    If Not (tdTemp Is Nothing) Then
        bResult = True
    End If

Exit_IsConnected:
    Set tdTemp = Nothing
    IsConnected = bResult
    Exit Function
    
Err_IsConnected:
    If Err.Number = 3265 Then
        Err.Clear
    Else
        Call ErrHandler("IsConnected function", Err.Number, Err.Description)
    End If
    Resume Exit_IsConnected
    
End Function

Public Sub ConnectTable(sTableName As String, sConnect As String, sSourceTable As String)
On Error GoTo Err_ConnectTable

    Dim tdfLinked As TableDef
    Dim rstLinked As DAO.Recordset
    Dim intTemp As Integer

    ' Create a new TableDef, set its Connect and
    ' SourceTableName properties based on the passed
    ' arguments, and append it to the TableDefs collection.
    Set tdfLinked = CurrentDb.CreateTableDef(sTableName)

    tdfLinked.Connect = sConnect
    tdfLinked.SourceTableName = sSourceTable
    CurrentDb.TableDefs.Append tdfLinked

    Set rstLinked = CurrentDb.OpenRecordset(sTableName)

Exit_ConnectTable:
    Exit Sub
    
Err_ConnectTable:
    'Stop
    Call ErrHandler("ConnectTable routine", Err.Number, Err.Description)
    Resume Exit_ConnectTable

End Sub