I originally posted this last week at Utter Access, but have not received any replies there.
Front end used to manage downloads and report writing. In this case, I want to import month end data files, push the table to the MonthEndDataTablesFY2019.accbd and then relink to the front-end. However, I keep getting Run-Time error 3024 and cannot get the table to export.
Here is my code so far:
Code:
Private Sub cmdImportDataFiles_Click()
Dim strMonthendDB As String
Dim strSQL As String
Dim rstData As DAO.Recordset
Dim strSourceDirectory As String
Dim strImportSpec As String
Dim strDownloadTable As String
Dim strDestination As String
Dim objFS As Object
Dim objFolder As Object
Dim objFiles As Object
Dim objFile As Object
strMonthendDB = "C:\MonthEndDataTables.accbd"
'Create SQL string to get list of all monthly source files
strSQL = ""
strSQL = strSQL & "SELECT SourceFileID, SourceFileDescription, SourceFileLocation, ImportSpecification, DownloadTableName, BackupLocation"
strSQL = strSQL & " FROM tblSourceFiles WHERE [ePremis]=False AND [Frequency]='Monthly'"
Set rstData = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
Do Until rstData.EOF
strSourceDirectory = rstData(2)
strImportSpec = rstData(3)
strDestination = rstData(5)
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strSourceDirectory)
Set objFiles = objFolder.Files
Select Case rstData(0)
Case 14 'Month End AR Summary
For Each objFile In objFiles
If Left(objFile.Name, 9) = "ARSummary" Then
strDownloadTable = rstData(4)
'Replace str "FacilityCode" with the facility code embedded in the File Name.
strDownloadTable = Replace(strDownloadTable, "FacilityCode", Mid(objFile.Name, InStr(objFile.Name, "_") + 1, 3))
'Replace str "MMMMYYYY" with the date embedded in the File Name.
strDownloadTable = Replace(strDownloadTable, "MMMMYYYY", MonthName(Mid(objFile.Name, 10, 2)) & Mid(objFile.Name, 14, 4))
DoCmd.TransferText acImportDelim, strImportSpec, strDownloadTable & "_Temp", objFile
MsgBox strMonthendDB
DoCmd.TransferDatabase acExport, "Microsoft Access", strMonthendDB, acTable, strDownloadTable & "_Temp", strDownloadTable, False
End If
Next objFile
Case 15 'Month End Financial Transaction Summary
Case 16 'Month End AR Detail
End Select
Set objFiles = Nothing
Set objFolder = Nothing
set objFS = Nothing
rstData.MoveNext
Loop
rstData.close
set rstData = Nothing
End Sub
The MsgBox returns the correct path.