So I'm currently trying to convert a foxpro .dbf table from an mrp system my company uses (PCMRP) to an excel spreadsheet via my Access application using ADO and an ODBC connection. I'm getting the following error: Could not find file "C:\users\temp\PartMast.mdb". See code below. In no way am i referencing PartMast.mdb in my code. When debugging, the docmd.transferspreadsheet line is where it is getting tripped up. I think it has to do with the tempQueryforExport because when the code stops at the docmd.transferspreadsheet line, the query shows up in the Access Objects in the left pane. When i try to run that query it gives the same error. Any ideas on why this is happening/ how to fix this?
Private Sub Command1_Click()
Dim strconnect As String
Dim cn As ADODB.Connection
Dim dbfFolder As String
Dim rs As ADODB.Recordset
Dim SQL As String
Set cn = New ADODB.Connection
Dim tempqueryname As String
Dim db As Database
Dim qdf As QueryDef
tempqueryname = "tmpQueryforExport"
dbfFolder = "C:\Users\temp\PC MRP"
strconnect = "DSN=pcMRPVFP;SourceDB=u:\PC MRP;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
filename = "PARTMAST.dbf"
Set cn = New ADODB.Connection
cn.Open strconnect
cn.Execute "SET REPROCESS TO 10 SECONDS"
Set rs = New ADODB.Recordset
SQL = "SELECT * from PartMast.dbf order by partno;"
rs.Open SQL, cn
Set db = CurrentDb
Set qdf = db.CreateQueryDef(tempqueryname)
qdf.SQL = SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tempqueryname, dbfFolder & "\" & Left(filename, 8) & ".xls", 1
rs.Close
Set rs = Nothing
Set cn = Nothing
db.QueryDefs.Delete tempqueryname
Set qdf = Nothing
Set db = Nothing
End Sub
So I realized where I went wrong... forget the ADO recordset entirely. I wasn't passing the ODBC connection string to my querydef.....
See working code below:
Private Sub Command1_Click()
Dim dbfFolder As String
Dim SQL As String
Dim tempqueryname As String
Dim db As Database
Dim qdf As QueryDef
tempqueryname = "tmpQueryforExport"
dbfFolder = "C:\Users\tburell\temp\PC MRP"
SQL = "SELECT * from PartMast.dbf order by partno;"
Set db = CurrentDb
On Error Resume Next
db.QueryDefs.Delete tempqueryname
On Error GoTo 0
Set qdf = db.CreateQueryDef(tempqueryname)
qdf.Connect = "ODBC;DSN=pcMRPVFP;SourceDB=u:\PC MRP;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
qdf.SQL = SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tempqueryname, dbfFolder & "\" & Left(filename, 8) & ".xls", 1
db.QueryDefs.Delete tempqueryname
Set qdf = Nothing
Set db = Nothing
end sub
For people struggling with the ODBC string what I did is link the table manually to access, open the table in design view, copy the string from the 'description' field in the table properties sheet, then paste it in your code.
If you obtained the string using the method i just described and you want to create the connection to a table directory rather than to a specific table, then just remove everything after 'Deleted=Yes;'. See below:
below creates a connection only to the table "partmast"
"ODBC;DSN=pcMRPVFP;SourceDB=u:\PC MRP;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE=partmast"
below creates a connection to U:\PCMRP which is a directory containing all my .dbf files
"ODBC;DSN=pcMRPVFP;SourceDB=u:\PC MRP;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
the latter connection string allows you to use the same connection string for different tables in that directory (in my case "U:/pcmrp"), just change your SQL string used to define your querydef to select different tables from that directory.