Chris said:
Hi Justin, and thanks for answering my post.
I've got the tables in SQL Server and my testing is complete
I developed the application in access then upsized it to SQL Server. I'm
not sure as of yet whether to use mdb or mde, but I'm pretty sure I'll be
using linked ODBC tables
This is where I'm stuck. I don't know how to turn access into a runtime
version and I don't know how to write the code to link to the runtime
version with SQL server.
does the SQL Server use, it will
Windows Authentication
I'm looking but I can't find any
Hi Chris
In order to create a runtime version, you need to have the Developer Edition
of MS Office (2000 and XP) which I believe you now purchase as 'Office
Developer Extensions' for Access 2003. Anyway, if you just have, say Office
Pro which comes with Access, there is no way to create an Access runtime
setup.
There is a lot that could be said about runtime installations, but there are
some downsides to installing Access like this (otherwise no-one would buy
the full product). These include requiring all errors to be properly
handled, no built-in tollbars or menus, no query by form and the list goes
on. If you have not tested your application as a runtime installation, then
you are not yet finished testing. You can simulate the runtime environment
by creating a shortcut to your database and specifying the runtime
parameter, e.g.
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"
"C:\MyDatabase.mdb" /RUNTIME
If this all sounds a bit daunting, is there any way the client would buy a
full version of Access? Otherwise you may need to buy another version
(unless you already have the Developer Edition).
Anyway, if you need to re-link the tables, you could paste the following
code into a new module and just call the function ReLinkTables. In this
version, you create a list of tables you want to link to, specifying what
they are called on the server and how you want to call them (and here we
have just chopped off the dbo prefix)
Public Function ReLinkTables() As Boolean
On Error GoTo Err_Handler
Dim strConnect As String
Dim strTableList As String
strConnect = "ODBC;Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=MyDatabase;" & _
"Trusted_Connection=yes;"
' Table list is in pairs like "SQL_Server|Link_Table;"
strTableList = "dbo.Customer|tblCustomers;" & _
"dbo.Product|tblProducts;" & _
"dbo.Order|tblOrders"
If DeleteLinks(strTableList) Then
If LinkTables(strTableList, strConnect) Then
ReLinkTables = True
End If
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function LinkTables(strTableList As String, strConnect As String) As
Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTwoTables As String
Dim strSqlServerTable As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long
Set dbs = CurrentDb
astrTables = Split(strTableList, ";")
For lngCount = 0 To UBound(astrTables())
strTwoTables = astrTables(lngCount)
lngPosition = InStr(strTwoTables, "|")
If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then
strSqlServerTable = Mid$(strTwoTables, 1, lngPosition - 1)
strLinkTable = Mid$(strTwoTables, lngPosition + 1)
Set tdf = dbs.CreateTableDef(strLinkTable)
tdf.Connect = strConnect
tdf.SourceTableName = strSqlServerTable
dbs.TableDefs.Append tdf
Set tdf = Nothing
End If
Next lngCount
LinkTables = True
Exit_Handler:
On Error Resume Next
If Not tdf Is Nothing Then
Set tdf = Nothing
End If
If Not tdf Is Nothing Then
Set dbs = Nothing
End If
Application.RefreshDatabaseWindow
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function DeleteLinks(strTableList As String) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTwoTables As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long
Dim blnError As Boolean
Set dbs = CurrentDb
astrTables = Split(strTableList, ";")
For lngCount = 0 To UBound(astrTables())
strTwoTables = astrTables(lngCount)
lngPosition = InStr(strTwoTables, "|")
If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then
strLinkTable = Mid$(strTwoTables, lngPosition + 1)
If TableExists(strLinkTable) Then
Set tdf = dbs.TableDefs(strLinkTable)
If Len(tdf.Connect) > 0 Then
dbs.TableDefs.Delete tdf.Name
Else
' This is not a linked table
MsgBox "Cannot delete table '" & strLinkTable & "'",
vbExclamation
blnError = True
End If
Set tdf = Nothing
End If
End If
Next lngCount
If Not blnError Then
DeleteLinks = True
End If
Exit_Handler:
On Error Resume Next
dbs.TableDefs.Refresh
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Application.RefreshDatabaseWindow
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function TableExists(strTableName As String) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Exit_Handler:
On Error Resume Next
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
