Hi All,
I am having runtime error 2046 The Command or action RunSQL isn't available on the line with red font. Thanks for any help and insight.
I am having runtime error 2046 The Command or action RunSQL isn't available on the line with red font. Thanks for any help and insight.
Rich (BB code):
Sub UploadRelevantColumns()
'The path to create the new access database
Dim StrPath As String
'An Access object
Dim ObjAccess As Object
StrPath = "C:\CAPITAL TEAM\WeeklyReport"
Set ObjAccess = CreateObject("Access.Application")
Call ObjAccess.NewCurrentDatabase(StrPath)
ObjAccess.Quit
Dim conn As ADODB.Connection
Dim Cat As ADOX.Catalog
Dim Tbl As ADOX.Table
' make sure to set up a reference to the Microsoft ActiveX Data Objects 6.1 Library
' and ADO Ext. 6.0 for DDL and Security
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.16.0;" & "Data Source=" & StrPath & ".accdb"
Set Cat = New ADOX.Catalog
Set Cat.ActiveConnection = conn
Set Tbl = New ADOX.Table
Tbl.Name = "FFRQuery"
Cat.Tables.Append Tbl
'ImportCSVFile
'With Tbl.Columns
'.Append "FAIN", adVarWChar, 10
'.Append "Category", adSmallInt
'.Append "InstallDate", adDate
'End With
Dim MyFile As FileDialog
Set MyFile = Application.FileDialog(msoFileDialogFilePicker)
'Browse for the Datasource and set the title of the dialog box.
With MyFile
.Title = "Browse for the relevant Report "
If .Show = True Then
' Assign the file to a variable Reportbk.
accessfilepath = MyFile.SelectedItems.Item(1)
Else
MsgBox "You clicked Canncel in the file dialog box.", , "Canceling the data extraction process"
Exit Sub
End If
End With
Dim StrFileName As String
StrFileName = Mid(accessfilepath, InStrRev(accessfilepath, "\", -1) + 1, Len(accessfilepath) - InStrRev(accessfilepath, "\", -1))
StrPath = "DATABASE=" & Left(accessfilepath, InStrRev(accessfilepath, "\", -1) - 1)
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT FAIN, ALI, Project, Activity, [Resource ID], [System Source] ,Voucher, Vendor, [Vendor Name], [RMB Amount], [UTL Amount], Type, Package INTO CurrentData FROM [Text;DATABASE=C:\Users\C033732\Desktop\WMATA Report Templates;HDR=Yes].Current.csv"
DoCmd.SetWarnings True
Set Cat = Nothing
conn.Close
Set conn = Nothing
End Sub