J
JohnC
My MS Access 2007 application has a routine that opens a file dialog box
using Application.FileDialog for the user to select a text file then it
establishes a connect string in a linked table and imports data from the
text file. I've noticed that if the selected text file is on a USB drive,
after I do the import, when I try to "Safely Remove Hardware" from the
system tray, I get the following error:
The device 'Generic volume' cannot be stopped right now. Try stopping the
device later.
Is dbsTemp.Close or db.Close missing somewhere? How can I use the immediate
window to see what is still open? Any help is much appreciated.
Code:
Public Function ap_OpenFile(strExt As String, strInitialFile) As String
' Requires reference to Microsoft Office 11.0 Object Library or later.
' This code works on 32 and 64 bit systems.
Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = "Please select a file"
.Filters.Clear
.Filters.Add "Text Files", strExt
.InitialFileName = strInitialFile
.InitialView = msoFileDialogViewDetails
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
ap_OpenFile = fDialog.SelectedItems(1)
Else
End If
End With
Set fDialog = Nothing
End Function
Sub ConnectOutput(dbsTemp As DAO.Database, _
strTable As String, strConnect As String, _
strSourceTable As String)
Dim tdfLinked As DAO.TableDef
Dim flgAddTable As Boolean
Dim strMessage As String
Set dbsTemp = CurrentDb
' Attempt to open the current link
' If there is no error, than flgAddTable is False and the link exist.
' Delete the link if it exists.
On Error Resume Next
Set tdfLinked = dbsTemp.TableDefs(strTable)
flgAddTable = Err.Number
If Not flgAddTable Then dbsTemp.TableDefs.Delete strTable
' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
On Error GoTo ErrorHandler
Set tdfLinked = dbsTemp.CreateTableDef(strTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked
Exit_ErrorHandler:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_ErrorHandler
End Sub
using Application.FileDialog for the user to select a text file then it
establishes a connect string in a linked table and imports data from the
text file. I've noticed that if the selected text file is on a USB drive,
after I do the import, when I try to "Safely Remove Hardware" from the
system tray, I get the following error:
The device 'Generic volume' cannot be stopped right now. Try stopping the
device later.
Is dbsTemp.Close or db.Close missing somewhere? How can I use the immediate
window to see what is still open? Any help is much appreciated.
Code:
Public Function ap_OpenFile(strExt As String, strInitialFile) As String
' Requires reference to Microsoft Office 11.0 Object Library or later.
' This code works on 32 and 64 bit systems.
Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = "Please select a file"
.Filters.Clear
.Filters.Add "Text Files", strExt
.InitialFileName = strInitialFile
.InitialView = msoFileDialogViewDetails
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
ap_OpenFile = fDialog.SelectedItems(1)
Else
End If
End With
Set fDialog = Nothing
End Function
Sub ConnectOutput(dbsTemp As DAO.Database, _
strTable As String, strConnect As String, _
strSourceTable As String)
Dim tdfLinked As DAO.TableDef
Dim flgAddTable As Boolean
Dim strMessage As String
Set dbsTemp = CurrentDb
' Attempt to open the current link
' If there is no error, than flgAddTable is False and the link exist.
' Delete the link if it exists.
On Error Resume Next
Set tdfLinked = dbsTemp.TableDefs(strTable)
flgAddTable = Err.Number
If Not flgAddTable Then dbsTemp.TableDefs.Delete strTable
' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
On Error GoTo ErrorHandler
Set tdfLinked = dbsTemp.CreateTableDef(strTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked
Exit_ErrorHandler:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_ErrorHandler
End Sub