Importing csv File into Access DB Using Excel VBA

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
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.

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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Where are you running this code from?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,847
Messages
5,598,435
Members
414,238
Latest member
juxion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top