I'm trying to create a dBase file since it seems the .TransferDatabase() method in my Export vba code requires the file to exist (keep getting "Field will not fit in record". Assuming due to the file not existing).
I came across one method online using vba:
http://edndoc.esri.com/arcobjects/8.3/samples/geodatabase/creating%20data/createnewdbf.htm
Public Function createDBF(strName As String, _
strFolder As String, _
Optional pFields As IFields) As ITable
' createDBF: simple function to create a DBASE file.
' note: the name of the DBASE file should not contain the .dbf extension
'
On Error GoTo EH
' Open the Workspace
Dim pFWS As IFeatureWorkspace
Dim pWorkspaceFactory As IWorkspaceFactory
Dim fs as object
Dim pFieldsEdit As esriCore.IFieldsEdit
Dim pFieldEdit As esriCore.IFieldEdit
Dim pField As IField
Set pWorkspaceFactory = New ShapefileWorkspaceFactory
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(strFolder) Then
MsgBox "Folder does not exist: " & vbCr & strFolder
Exit Function
End If
Set pFWS = pWorkspaceFactory.OpenFromFile(strFolder, 0)
' if a fields collection is not passed in then create one
If pFields Is Nothing Then
' create the fields used by our object
Set pFields = New esriCore.Fields
Set pFieldsEdit = pFields
pFieldsEdit.FieldCount = 1
'Create text Field
Set pField = New Field
Set pFieldEdit = pField
With pFieldEdit
.Length = 30
.Name = "TextField"
.Type = esriFieldTypeString
End With
Set pFieldsEdit.Field(0) = pField
End If
Set createDBF = pFWS.CreateTable(strName, pFields, Nothing, Nothing, "")
Exit Function
EH:
MsgBox Err.Description, vbInformation, "createDBF"
End Function
but when trying to run it just for testing out the below line receives "Compile error: User-defined type not defined":
Public Function createDBF(strName As String, _
strFolder As String, _
Optional pFields As IFields) As ITable
Another thread (from 2003...) suggested a query could be used to Export my Access Table to dBase file:
http://www.pcreview.co.uk/forums/creating-dbf-file-using-access-vba-code-t1077674.html
but my syntax appears to be off, receiving "Syntax error in query. Incomplete query clause:
SELECT ImportedData.*
INTO "...filePath\Personal Project Notes\" "dBase IV;"
FROM ImportedData;
If anyone has any ideas to fix these or even a different solution I'm all ears. Again, trying to create the dBase file since my following export code flags the .TransferDatabase() method saying "Field will not fit in record":
Export Code:
Sub Export()
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long
'Go to the worksheet containing the records you want to transfer.
Worksheets("FeedSamples").Activate
'Determine the last row of data based on column A.
LastRow = Cells(Rows.Count, 1).End(xlUp).row
'Create the connection to the database.
Set dbConnection = New ADODB.Connection
'Define the database file name
dbFileName = "...filePath\Personal Project Notes\FeedSampleResults.accdb"
'Define the Provider and open the connection.
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
";Persist Security Info=False;"
.Open dbFileName
End With
'Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="ImportedData", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
'Loop thru rows & columns to load records from Excel to Access.
'Assume row 1 is the header row, so start at row 2.
'ACCESS COLUMNS MUST BE NAMED EXACTLY THE SAME AS EXCEL COLUMNS
For xRow = 2 To LastRow
dbRecordset.AddNew
'Assume this is an 8-column (field) table starting with column A.
For xColumn = 1 To 69
dbRecordset(Cells(1, xColumn).value) = Cells(xRow, xColumn).value
Next xColumn
dbRecordset.Update
Next xRow
'Close the connections.
dbRecordset.Close
dbConnection.Close
'Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing
'Optional:
'Clear the range of data (the records) you just transferred.
'Range("A2:H" & LastRow).ClearContents
MsgBox "Test"
Dim acx As access.Application
Set acx = New access.Application
acx.OpenCurrentDatabase ("...filePath\Personal Project Notes\FeedSampleResults.accdb")
acx.DoCmd.TransferDatabase acExport, "dBase IV", "...filePath\Personal Project Notes\", acTable, "ImportedData", "TEST.DBF"
acx.CloseCurrentDatabase
End Sub
The filepath is correct, but currently TEST.DBF does not exist. I was originally assuming the method created the file for me...
EDIT:
After some testing, I shrunk my Access table down to 15 fields TEXT at 255 characters. When running the Access gui-Export I was successfully able to create a dBase IV file with my 15 fields. Since I then had my .DBF file, I placed it in my .TransferDatabase() file path and my vba code in Excel executed successfully from Excel to Access to dBase file!
I now just need to figure out what the issue is when exporting 69 records versus 15....
Any thoughts?
.transferdatabase()from what to what? – Skip IntrodBasechar fields length 254, rather than 255? – Skip Intro