Button to Import and run Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a button on a form that will prompt the user to select a
..csv file which will then be imported using an import specification and then
queries run against the imported file. I'm fine with the queries etc, it's
just the import one-click process.

I've managed to get a dialog box to run using
http://www.mvps.org/access/api/api0001.htm, but when I select the file to
import, nothing happens.

I think I need to build this process into a macro so the import spec can be
defined and the additional queries defined.

Am running Access 2003 and ashamed to say my VBA knowledge/skills is
virtually non-existent.

Thanks
 
Here is an example using the code you downloaded. Create a command button on
your form. In the Properties Dialog for the button, select the Events tab.
Select Code builder, and paste this in. You will need to modify it to suit
your names. Also, this imports an Excel spreadsheet so you will have to make
some modifications to look for .csv files and use the TransferText instead.

Dim strDefaultDir As String
Dim strFileName As String
Dim strFilter As String
Dim lngFlags As Long
Dim strGetFileName As String

On Error GoTo cmdImport_Click_Error


'Set up the Default path and file
strDefaultDir = "\\rsltx1-bm01\busmgmt\Arthur\"
strFileName = "IQ Navigator.xls"
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Call the Open File Dialog
Do While True
strGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Import IQ Navigator")
Me.Repaint
If Len(strGetFileName) = 0 Then 'User Clicked CANCEL
If MsgBox("Cancel Import", vbQuestion + vbYesNo, _
"Import IQ Navigator") = vbYes Then
MsgBox "Import Canceled", , "Import IQ Navigator"
Exit Do
End If
Else
CurrentDb.Execute ("DELETE * FROM Received_File;"), dbFailOnError
DoCmd.TransferSpreadsheet acImport, , "Received_File",
strGetFileName, True
Call RemoveNullRecs
Me.txtTotalHours = DSum("[total hours]", "received_file", _
"[pc code] like '*D4056*'")
MsgBox "Import Complete", , "Import IQ Navigator"
Exit Do
End If
Loop

cmdImport_Click_Exit:

On Error Resume Next
Exit Sub

cmdImport_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure cmdImport_Click of VBA Document
Form_frmImportIQNavigator"
GoTo cmdImport_Click_Exit
 
I have managed to get the import etc to work:

Private Sub Command51_Click()
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, _
"CSV Files (*.csv)", "*.csv")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select the .csv file to Import...", _
Flags:=ahtOFN_HIDEREADONLY)
DoCmd.TransferText acImportDelim, "Trust Episode Upload", "Trust
Upload", strInputFileName, False, ""

End Sub

Two issues.

Number 1, how to I get the queries to run after this import process? At the
moment, I have one button for the import and one to run the queries.

Number 2, when I choose to select 'Cancel' rather than the file to import, I
get the following error ' Run-time error '2522': The action or method
requires a File Name argument.

Klatuu said:
Here is an example using the code you downloaded. Create a command button on
your form. In the Properties Dialog for the button, select the Events tab.
Select Code builder, and paste this in. You will need to modify it to suit
your names. Also, this imports an Excel spreadsheet so you will have to make
some modifications to look for .csv files and use the TransferText instead.

Dim strDefaultDir As String
Dim strFileName As String
Dim strFilter As String
Dim lngFlags As Long
Dim strGetFileName As String

On Error GoTo cmdImport_Click_Error


'Set up the Default path and file
strDefaultDir = "\\rsltx1-bm01\busmgmt\Arthur\"
strFileName = "IQ Navigator.xls"
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Call the Open File Dialog
Do While True
strGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Import IQ Navigator")
Me.Repaint
If Len(strGetFileName) = 0 Then 'User Clicked CANCEL
If MsgBox("Cancel Import", vbQuestion + vbYesNo, _
"Import IQ Navigator") = vbYes Then
MsgBox "Import Canceled", , "Import IQ Navigator"
Exit Do
End If
Else
CurrentDb.Execute ("DELETE * FROM Received_File;"), dbFailOnError
DoCmd.TransferSpreadsheet acImport, , "Received_File",
strGetFileName, True
Call RemoveNullRecs
Me.txtTotalHours = DSum("[total hours]", "received_file", _
"[pc code] like '*D4056*'")
MsgBox "Import Complete", , "Import IQ Navigator"
Exit Do
End If
Loop

cmdImport_Click_Exit:

On Error Resume Next
Exit Sub

cmdImport_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure cmdImport_Click of VBA Document
Form_frmImportIQNavigator"
GoTo cmdImport_Click_Exit




Andy said:
I want to create a button on a form that will prompt the user to select a
.csv file which will then be imported using an import specification and then
queries run against the imported file. I'm fine with the queries etc, it's
just the import one-click process.

I've managed to get a dialog box to run using
http://www.mvps.org/access/api/api0001.htm, but when I select the file to
import, nothing happens.

I think I need to build this process into a macro so the import spec can be
defined and the additional queries defined.

Am running Access 2003 and ashamed to say my VBA knowledge/skills is
virtually non-existent.

Thanks
Click to expand...
Click to expand...
 
Andy said:
I have managed to get the import etc to work:

Private Sub Command51_Click()
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, _
"CSV Files (*.csv)", "*.csv")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select the .csv file to Import...", _
Flags:=ahtOFN_HIDEREADONLY)
DoCmd.TransferText acImportDelim, "Trust Episode Upload", "Trust
Upload", strInputFileName, False, ""

End Sub

Two issues.

Number 1, how to I get the queries to run after this import process? At the
moment, I have one button for the import and one to run the queries.
Click to expand...

It depends on what kind of queries they are are. If they are action queries
(Append, Update, Delete), then you can run them using the code below. (I
added the code to the fix for problem 2)
Number 2, when I choose to select 'Cancel' rather than the file to import, I
get the following error ' Run-time error '2522': The action or method
requires a File Name argument.
Click to expand...

When you cancel the Open Dialog, it returns a zero length string ( "" ).
Now you are trying to run the transfer text with no file name, so you get the
error. Here is the fix:

If Len(strInputFileName) > 0 Then
DoCmd.TransferText acImportDelim, "Trust Episode Upload", "Trust
Upload", strInputFileName, False, ""
CurrentDb.Execute "MyFirstActionQuery", dbFailOnError
CurrentEb.Execute "MySecondActionQuery", dbFailOnError
End If

Now it will only run if it has a file name to import.
Klatuu said:
Here is an example using the code you downloaded. Create a command button on
your form. In the Properties Dialog for the button, select the Events tab.
Select Code builder, and paste this in. You will need to modify it to suit
your names. Also, this imports an Excel spreadsheet so you will have to make
some modifications to look for .csv files and use the TransferText instead.

Dim strDefaultDir As String
Dim strFileName As String
Dim strFilter As String
Dim lngFlags As Long
Dim strGetFileName As String

On Error GoTo cmdImport_Click_Error


'Set up the Default path and file
strDefaultDir = "\\rsltx1-bm01\busmgmt\Arthur\"
strFileName = "IQ Navigator.xls"
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
'Call the Open File Dialog
Do While True
strGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Import IQ Navigator")
Me.Repaint
If Len(strGetFileName) = 0 Then 'User Clicked CANCEL
If MsgBox("Cancel Import", vbQuestion + vbYesNo, _
"Import IQ Navigator") = vbYes Then
MsgBox "Import Canceled", , "Import IQ Navigator"
Exit Do
End If
Else
CurrentDb.Execute ("DELETE * FROM Received_File;"), dbFailOnError
DoCmd.TransferSpreadsheet acImport, , "Received_File",
strGetFileName, True
Call RemoveNullRecs
Me.txtTotalHours = DSum("[total hours]", "received_file", _
"[pc code] like '*D4056*'")
MsgBox "Import Complete", , "Import IQ Navigator"
Exit Do
End If
Loop

cmdImport_Click_Exit:

On Error Resume Next
Exit Sub

cmdImport_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure cmdImport_Click of VBA Document
Form_frmImportIQNavigator"
GoTo cmdImport_Click_Exit




Andy said:
I want to create a button on a form that will prompt the user to select a
.csv file which will then be imported using an import specification and then
queries run against the imported file. I'm fine with the queries etc, it's
just the import one-click process.

I've managed to get a dialog box to run using
http://www.mvps.org/access/api/api0001.htm, but when I select the file to
import, nothing happens.

I think I need to build this process into a macro so the import spec can be
defined and the additional queries defined.

Am running Access 2003 and ashamed to say my VBA knowledge/skills is
virtually non-existent.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top