By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,728 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Can't convert to MDE after adding refresh links functions

P: n/a
ET
I don't know whats the problem, but after I added functions
to first verify, then relink linked tables if not found,
now I can't convert that database to MDE format.
I can split the database, but can't convert
part of the database with forms, reports, queries to MDE format.

Can somebody advice on this?

References, in the order, from the top:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft ADO Ext. 2.7 for DDL and Security
Microsoft ActiveX Data Objects 2.7 Library
Here is the error signature:

Error signature
AppName: msaccess.exe AppVer: 10.0.6501.0 ModName mso.dll
ModVer: 10.0.6735.0 Offset: 000099bb

Here is the VBA code for relinking, it is in LOAD event
of starting switchboard form:
============================================

Private Sub Form_Load()

DoCmd.Hourglass True

Call LinkTables

DoCmd.Hourglass False

End Sub

=========================================
Sub LinkTables()

Dim objFileDialog As FileDialog

On Error GoTo LinkTables_Err:

DoCmd.Hourglass True

'Check to see if tables are linked properly
If Not VerifyLink Then

'If still not ok, attempt to relink with expected file name
If Not ReLink(CurrentProject.FullName, True) Then

MsgBox "Date file for this database can not be found." +
(Chr(13) & Chr(10)) + (Chr(13) & Chr(10)) + "After you click button OK,
File dialog window will pop up" + (Chr(13)) + "so you can specify the
location of the data file."

'If still not ok, ask user to locate file
Set objFileDialog = FileDialog(msoFileDialogOpen)

With objFileDialog
.AllowMultiSelect = False
.Show
End With

'Attempt to link to file user selected
If Not ReLink(objFileDialog.SelectedItems(1), False) Then

'If not successful, display a message and quit
application
MsgBox "You Cannot Run This App Without Locating
Data Tables"
DoCmd.Close acForm, "frmSplash"
DoCmd.Quit
End If
End If
End If

DoCmd.Hourglass False
Exit Sub

LinkTables_Err:
DoCmd.Hourglass False
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Sub

End Sub

=====================================
Function VerifyLink() As Boolean
'Verify connection information in linked tables.

'Declare required variables
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim strTemp As String

'Point database object variable at the current database
Set cat = New ADOX.Catalog

With cat
Set .ActiveConnection = CurrentProject.Connection

'Continue if links are broken
On Error Resume Next

'Open one linked table to see if connection information is
correct
For Each tdf In .Tables
If tdf.Type = "LINK" Then
strTemp = tdf.Columns(0).Name
If Err.Number Then
Exit For
End If
End If
Next tdf

End With

VerifyLink = (Err.Number = 0)

End Function

============================================
Function ReLink(strDir As String, DefaultData As Boolean) As Boolean
'Relink a broken linked Access table.

'Declare required variables
Dim cat As ADOX.Catalog
Dim tdfRelink As ADOX.Table
Dim strPath As String
Dim strName As String
Dim intCounter As Integer
Dim vntStatus As Variant

'Prepare status bar
vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

Set cat = New ADOX.Catalog

With cat
.ActiveConnection = CurrentProject.Connection

On Error Resume Next
'Update progress meter
Call SysCmd(acSysCmdInitMeter, "Linking Data Tables",
..Tables.Count)

'Loop through each table, attempting to update the link
For Each tdfRelink In .Tables
intCounter = intCounter + 1
Call SysCmd(acSysCmdUpdateMeter, intCounter)
If .Tables(tdfRelink.Name).Type = "LINK" And
Left(tdfRelink.Name, 3) = "tbl" Then
tdfRelink.Properties("Jet OLEDB:Link Datasource") =
strDir
End If

If Err.Number Then
Exit For
End If
Next tdfRelink

End With

'Reset the progress meter
Call SysCmd(acSysCmdRemoveMeter)

vntStatus = SysCmd(acSysCmdClearStatus)

ReLink = (Err = 0)

End Function
================================================== ==

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
In the code window (Alt+F11), go to Debug|Compile. Is the database compiled?
It needs to be in order to create the mde file. If not, do you get any
errors when you try to compile?

Where are you trying to save the mde file to? I've see problems if trying to
save to a network share. If so, save to a local directory then copy the file
to the network share.

Have you installed all of the updates for Office XP? Go to
http://office.microsoft.com/en-us/of...e/default.aspx and click Check
for Updates.

--
Wayne Morgan
MS Access MVP
"ET" <ve****@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I don't know whats the problem, but after I added functions
to first verify, then relink linked tables if not found,
now I can't convert that database to MDE format.
I can split the database, but can't convert
part of the database with forms, reports, queries to MDE format.

Can somebody advice on this?

References, in the order, from the top:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft ADO Ext. 2.7 for DDL and Security
Microsoft ActiveX Data Objects 2.7 Library
Here is the error signature:

Error signature
AppName: msaccess.exe AppVer: 10.0.6501.0 ModName mso.dll
ModVer: 10.0.6735.0 Offset: 000099bb

Here is the VBA code for relinking, it is in LOAD event
of starting switchboard form:
============================================

Private Sub Form_Load()

DoCmd.Hourglass True

Call LinkTables

DoCmd.Hourglass False

End Sub

=========================================
Sub LinkTables()

Dim objFileDialog As FileDialog

On Error GoTo LinkTables_Err:

DoCmd.Hourglass True

'Check to see if tables are linked properly
If Not VerifyLink Then

'If still not ok, attempt to relink with expected file name
If Not ReLink(CurrentProject.FullName, True) Then

MsgBox "Date file for this database can not be found." +
(Chr(13) & Chr(10)) + (Chr(13) & Chr(10)) + "After you click button OK,
File dialog window will pop up" + (Chr(13)) + "so you can specify the
location of the data file."

'If still not ok, ask user to locate file
Set objFileDialog = FileDialog(msoFileDialogOpen)

With objFileDialog
.AllowMultiSelect = False
.Show
End With

'Attempt to link to file user selected
If Not ReLink(objFileDialog.SelectedItems(1), False) Then

'If not successful, display a message and quit
application
MsgBox "You Cannot Run This App Without Locating
Data Tables"
DoCmd.Close acForm, "frmSplash"
DoCmd.Quit
End If
End If
End If

DoCmd.Hourglass False
Exit Sub

LinkTables_Err:
DoCmd.Hourglass False
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Sub

End Sub

=====================================
Function VerifyLink() As Boolean
'Verify connection information in linked tables.

'Declare required variables
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim strTemp As String

'Point database object variable at the current database
Set cat = New ADOX.Catalog

With cat
Set .ActiveConnection = CurrentProject.Connection

'Continue if links are broken
On Error Resume Next

'Open one linked table to see if connection information is
correct
For Each tdf In .Tables
If tdf.Type = "LINK" Then
strTemp = tdf.Columns(0).Name
If Err.Number Then
Exit For
End If
End If
Next tdf

End With

VerifyLink = (Err.Number = 0)

End Function

============================================
Function ReLink(strDir As String, DefaultData As Boolean) As Boolean
'Relink a broken linked Access table.

'Declare required variables
Dim cat As ADOX.Catalog
Dim tdfRelink As ADOX.Table
Dim strPath As String
Dim strName As String
Dim intCounter As Integer
Dim vntStatus As Variant

'Prepare status bar
vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

Set cat = New ADOX.Catalog

With cat
.ActiveConnection = CurrentProject.Connection

On Error Resume Next
'Update progress meter
Call SysCmd(acSysCmdInitMeter, "Linking Data Tables",
.Tables.Count)

'Loop through each table, attempting to update the link
For Each tdfRelink In .Tables
intCounter = intCounter + 1
Call SysCmd(acSysCmdUpdateMeter, intCounter)
If .Tables(tdfRelink.Name).Type = "LINK" And
Left(tdfRelink.Name, 3) = "tbl" Then
tdfRelink.Properties("Jet OLEDB:Link Datasource") =
strDir
End If

If Err.Number Then
Exit For
End If
Next tdfRelink

End With

'Reset the progress meter
Call SysCmd(acSysCmdRemoveMeter)

vntStatus = SysCmd(acSysCmdClearStatus)

ReLink = (Err = 0)

End Function
================================================== ==

Nov 13 '05 #2

P: n/a
On 20 Oct 2005 02:00:11 -0700, "ET" <ve****@yahoo.com> wrote:

Your Access installation may be messed up. Try this:
Copy the app to another machine.
Code window > Debug > Compile
If no problems, then convert to MDE.

-Tom.

I don't know whats the problem, but after I added functions
to first verify, then relink linked tables if not found,
now I can't convert that database to MDE format.
I can split the database, but can't convert
part of the database with forms, reports, queries to MDE format.

Can somebody advice on this?

References, in the order, from the top:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft ADO Ext. 2.7 for DDL and Security
Microsoft ActiveX Data Objects 2.7 Library
Here is the error signature:

Error signature
AppName: msaccess.exe AppVer: 10.0.6501.0 ModName mso.dll
ModVer: 10.0.6735.0 Offset: 000099bb

Here is the VBA code for relinking, it is in LOAD event
of starting switchboard form:
============================================

Private Sub Form_Load()

DoCmd.Hourglass True

Call LinkTables

DoCmd.Hourglass False

End Sub

=========================================
Sub LinkTables()

Dim objFileDialog As FileDialog

On Error GoTo LinkTables_Err:

DoCmd.Hourglass True

'Check to see if tables are linked properly
If Not VerifyLink Then

'If still not ok, attempt to relink with expected file name
If Not ReLink(CurrentProject.FullName, True) Then

MsgBox "Date file for this database can not be found." +
(Chr(13) & Chr(10)) + (Chr(13) & Chr(10)) + "After you click button OK,
File dialog window will pop up" + (Chr(13)) + "so you can specify the
location of the data file."

'If still not ok, ask user to locate file
Set objFileDialog = FileDialog(msoFileDialogOpen)

With objFileDialog
.AllowMultiSelect = False
.Show
End With

'Attempt to link to file user selected
If Not ReLink(objFileDialog.SelectedItems(1), False) Then

'If not successful, display a message and quit
application
MsgBox "You Cannot Run This App Without Locating
Data Tables"
DoCmd.Close acForm, "frmSplash"
DoCmd.Quit
End If
End If
End If

DoCmd.Hourglass False
Exit Sub

LinkTables_Err:
DoCmd.Hourglass False
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Sub

End Sub

=====================================
Function VerifyLink() As Boolean
'Verify connection information in linked tables.

'Declare required variables
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim strTemp As String

'Point database object variable at the current database
Set cat = New ADOX.Catalog

With cat
Set .ActiveConnection = CurrentProject.Connection

'Continue if links are broken
On Error Resume Next

'Open one linked table to see if connection information is
correct
For Each tdf In .Tables
If tdf.Type = "LINK" Then
strTemp = tdf.Columns(0).Name
If Err.Number Then
Exit For
End If
End If
Next tdf

End With

VerifyLink = (Err.Number = 0)

End Function

============================================
Function ReLink(strDir As String, DefaultData As Boolean) As Boolean
'Relink a broken linked Access table.

'Declare required variables
Dim cat As ADOX.Catalog
Dim tdfRelink As ADOX.Table
Dim strPath As String
Dim strName As String
Dim intCounter As Integer
Dim vntStatus As Variant

'Prepare status bar
vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

Set cat = New ADOX.Catalog

With cat
.ActiveConnection = CurrentProject.Connection

On Error Resume Next
'Update progress meter
Call SysCmd(acSysCmdInitMeter, "Linking Data Tables",
.Tables.Count)

'Loop through each table, attempting to update the link
For Each tdfRelink In .Tables
intCounter = intCounter + 1
Call SysCmd(acSysCmdUpdateMeter, intCounter)
If .Tables(tdfRelink.Name).Type = "LINK" And
Left(tdfRelink.Name, 3) = "tbl" Then
tdfRelink.Properties("Jet OLEDB:Link Datasource") =
strDir
End If

If Err.Number Then
Exit For
End If
Next tdfRelink

End With

'Reset the progress meter
Call SysCmd(acSysCmdRemoveMeter)

vntStatus = SysCmd(acSysCmdClearStatus)

ReLink = (Err = 0)

End Function
================================================= ===


Nov 13 '05 #3

P: n/a
ET
Thank you Tom and Wayne,

I found solution for this problem here:
http://support.microsoft.com/?id=814858

the only difference is, on step 1. instead of running /decompile from
Start/Run,
I run it from Command prompt...

It works fine, converts to MDE.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.