title: How to create dBase (IV?) file?
body: <p>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).</p>
  
  <p>I came across one method online using vba:</p>
  
  <p><a href="http://edndoc.esri.com/arcobjects/8.3/samples/geodatabase/creating%20data/createnewdbf.htm" rel="nofollow">http://edndoc.esri.com/arcobjects/8.3/samples/geodatabase/creating%20data/createnewdbf.htm</a></p>
  
  <pre><code>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: " &amp; vbCr &amp; 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
  </code></pre>
  
  <p>but when trying to run it just for testing out the below line receives "Compile error: User-defined type not defined":</p>
  
  <pre><code>Public Function createDBF(strName As String, _
                        strFolder As String, _
               Optional pFields As IFields) As ITable
  </code></pre>
  
  <p>Another thread (from 2003...) suggested a query could be used to Export my Access Table to dBase file:</p>
  
  <p><a href="http://www.pcreview.co.uk/forums/creating-dbf-file-using-access-vba-code-t1077674.html" rel="nofollow">http://www.pcreview.co.uk/forums/creating-dbf-file-using-access-vba-code-t1077674.html</a></p>
  
  <p>but my syntax appears to be off, receiving "Syntax error in query. Incomplete query clause:</p>
  
  <pre><code>SELECT ImportedData.*
  INTO "...filePath\Personal Project Notes\" "dBase IV;"
  FROM ImportedData;
  </code></pre>
  
  <p>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":</p>
  
  <p><strong>Export Code:</strong></p>
  
  <pre><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=" &amp; dbFileName &amp; _
      ";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 &amp; 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" &amp; 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
  </code></pre>
  
  <p>The filepath is correct, but currently TEST.DBF does not exist. I was originally assuming the method created the file for me...</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>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!</p>
  
  <p>I now just need to figure out what the issue is when exporting 69 records versus 15....</p>
  
  <p>Any thoughts?</p>