none
Import DBF files into Access

    Question

  • Import DBF files into Access

    Apparently this has changed significantly from VB6. I provided my VB 6 Code below, but wondering how to do this in VB 2010 Express.

    Dim acApp As Access.Application
    Set acApp = New Access.Application

        acApp.OpenCurrentDatabase "D:\s\Job.mdb", False
        acApp.DoCmd.TransferDatabase acImport, "dBASE III", "D:\s\Job.DBF", "Job"

        acApp.DoCmd.OpenQuery "Query1" ' Updates Data
        acApp.DoCmd.OpenQuery "Query2" ' Creates Table
        acApp.DoCmd.OpenQuery "Query3" ' Appends Data to New table
        acApp.DoCmd.OpenQuery "Query4" ' More updates to new table
        
        acApp.DoCmd.TransferDatabase acExport, "Microsoft Access", "D:\S\Job.mdb", acTable, "NewTable", NewDbf
        
        acApp.CloseCurrentDatabase

    Glenn

    Thursday, July 12, 2012 7:15 PM

Answers

  • I got it, unless there is a better. Here is what I did.

    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Access
    Imports Microsoft.VisualBasic
    Imports System
    Imports System.Diagnostics
    Imports System.IO
    Imports System.Windows.Forms

    Dim acApp As New Microsoft.Office.Interop.Access.Application

            acApp.OpenCurrentDatabase("D:\S\Test\Test.mdb", False)
            acApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, _
                                         "dBASE III", "D:\S\Test\", Microsoft.Office.Interop.Access.AcObjectType.acTable, _
                                         "Test.dbf", "Job")


            acApp.DoCmd.OpenQuery("Query1") ' Updates Data
            acApp.DoCmd.OpenQuery("Query2") ' Creates Table
            acApp.DoCmd.OpenQuery("Query3") ' Appends Data to New table
            acApp.DoCmd.OpenQuery("Query4") ' More updates to new table

            acApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acExport, "dBASE IV", _
                                         "D:\S\Test\", Microsoft.Office.Interop.Access.AcObjectType.acTable, "NewJob", _
                                         "NewDBF")

            acApp.CloseCurrentDatabase()


    Good you have this working. I made the suggestion for OleDb only because we used the same code you have and ended up with several computers it failed to work on in short from a DLL conflict. Any ways good you are functioning now.

    KSG

    • Marked as answer by Broggy79 Thursday, July 12, 2012 9:53 PM
    Thursday, July 12, 2012 9:20 PM

All replies

  • Hello,

    Your code is actually Office automation. What you could do instead is read the dBase file using OleDb where the connection string DataSource property points to the folder containing the dbf file. In the example below we open test.dbf in C:\Data and load the data into a DataTable. Once the data is loaded you would connect to your MS-Access database using another OleDbConnection or repurpose the first one by first closing the connection then changing the connection string for the MS-Acces database (see example for a connection to MS-Access below).  You would then create an OleDbCommand with SQL statement to insert data into your table.

    A slight alteration to the above is to have the dbf and mdb open at once and loop thru rows in the dbf file using a data reader which would be used to insert data into your MS-Access file using as suggested above an OleDbCommand setup with an insert SQL statement.

    If IO.File.Exists("C:\Data\TEST.DBF") Then
        Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                            "Data Source=C:\Data;" & _
                                            "Extended Properties=dBase III")
        cn.Open()
        Dim cmd As New OleDb.OleDbCommand
        cmd.CommandText = "SELECT * FROM TEST"
        cmd.Connection = cn
        Dim dt As New DataTable
        dt.Load(cmd.ExecuteReader)
    End If

    Setting up a connection to an MS-Access database (in this case no path is given the the database would be in the same folder as the executable or prefix a path to the file name)

    Dim cn As New OleDb.OleDbConnection With {.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.mdb"}


    KSG

    Thursday, July 12, 2012 7:34 PM
  • The code runs without errors, but it is not import my test.dbf file. You also mentioned, "Setting up a connection to an MS-Access database (in this case no path is given the the database would be in the same folder as the executable or prefix a path to the file name)" So is there way to use a specific access file? D:\S\Test\Tes.mdb ?

    Below is what I tried, but it does not import the DBase file.

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            If IO.File.Exists("D:\S\Test\TEST.DBF") Then
                Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                    "Data Source=D:\S\Test\;" & _
                                                    "Extended Properties=dBase III")
                cn.Open()
                Dim cmd As New OleDb.OleDbCommand
                cmd.CommandText = "SELECT * FROM TEST"
                cmd.Connection = cn
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
            End If
        End Sub

    Thursday, July 12, 2012 7:45 PM
  • I got it, unless there is a better. Here is what I did.

    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Access
    Imports Microsoft.VisualBasic
    Imports System
    Imports System.Diagnostics
    Imports System.IO
    Imports System.Windows.Forms

    Dim acApp As New Microsoft.Office.Interop.Access.Application

            acApp.OpenCurrentDatabase("D:\S\Test\Test.mdb", False)
            acApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, _
                                         "dBASE III", "D:\S\Test\", Microsoft.Office.Interop.Access.AcObjectType.acTable, _
                                         "Test.dbf", "Job")


            acApp.DoCmd.OpenQuery("Query1") ' Updates Data
            acApp.DoCmd.OpenQuery("Query2") ' Creates Table
            acApp.DoCmd.OpenQuery("Query3") ' Appends Data to New table
            acApp.DoCmd.OpenQuery("Query4") ' More updates to new table

            acApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acExport, "dBASE IV", _
                                         "D:\S\Test\", Microsoft.Office.Interop.Access.AcObjectType.acTable, "NewJob", _
                                         "NewDBF")

            acApp.CloseCurrentDatabase()

    Thursday, July 12, 2012 8:42 PM
  • The code runs without errors, but it is not import my test.dbf file. You also mentioned, "Setting up a connection to an MS-Access database (in this case no path is given the the database would be in the same folder as the executable or prefix a path to the file name)" So is there way to use a specific access file? D:\S\Test\Tes.mdb ?

    Below is what I tried, but it does not import the DBase file.

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            If IO.File.Exists("D:\S\Test\TEST.DBF") Then
                Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                    "Data Source=D:\S\Test\;" & _
                                                    "Extended Properties=dBase III")
                cn.Open()
                Dim cmd As New OleDb.OleDbCommand
                cmd.CommandText = "SELECT * FROM TEST"
                cmd.Connection = cn
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
            End If
        End Sub


    So you checked dt.Rows.Count and it returned 0 ? I only ask because the example provided was from an actual example project I did and just tried the code again and it returns all rows.

    KSG

    Thursday, July 12, 2012 9:19 PM
  • I got it, unless there is a better. Here is what I did.

    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Access
    Imports Microsoft.VisualBasic
    Imports System
    Imports System.Diagnostics
    Imports System.IO
    Imports System.Windows.Forms

    Dim acApp As New Microsoft.Office.Interop.Access.Application

            acApp.OpenCurrentDatabase("D:\S\Test\Test.mdb", False)
            acApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, _
                                         "dBASE III", "D:\S\Test\", Microsoft.Office.Interop.Access.AcObjectType.acTable, _
                                         "Test.dbf", "Job")


            acApp.DoCmd.OpenQuery("Query1") ' Updates Data
            acApp.DoCmd.OpenQuery("Query2") ' Creates Table
            acApp.DoCmd.OpenQuery("Query3") ' Appends Data to New table
            acApp.DoCmd.OpenQuery("Query4") ' More updates to new table

            acApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acExport, "dBASE IV", _
                                         "D:\S\Test\", Microsoft.Office.Interop.Access.AcObjectType.acTable, "NewJob", _
                                         "NewDBF")

            acApp.CloseCurrentDatabase()


    Good you have this working. I made the suggestion for OleDb only because we used the same code you have and ended up with several computers it failed to work on in short from a DLL conflict. Any ways good you are functioning now.

    KSG

    • Marked as answer by Broggy79 Thursday, July 12, 2012 9:53 PM
    Thursday, July 12, 2012 9:20 PM
  • BTW, this can be accomplished rather easily without using Access automation:

            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\S\Job.mdb")
    
            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S].[Job]", AccessConn)
    
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    This will create a new table in the Access database called Job. It is also possible to add data to an existing table using an INSERT statement.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, July 13, 2012 3:29 PM
  • I will try that tomorrow and see what happens.

    Thank you

    Glenn

    Tuesday, July 17, 2012 2:50 AM
  • When I try this, I get the error below. We have Office 2010 if that makes any difference. 

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147467259
      Message=Could not find installable ISAM.
      Source=Microsoft JET Database Engine
      StackTrace:
           at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
           at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
           at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
           at System.Data.OleDb.OleDbConnection.Open()
           at WindowsApplication1.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb:line 6
           at System.Windows.Forms.Control.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Tuesday, July 17, 2012 2:06 PM
  • If your app is running 64-bit it would not be possible to use the Jet OLEDB Provider, however; it is possible to run the app 32-bit under 64-bit Windows by changing the Platform option (Build...Configuration Manager...) to x86.

    Also, if your Access database is 2007/2010 (e.g. .accdb file) then you would need to use the ACE OLEDB Provider in the connection string:

    Provider=Microsoft.ACE.OLEDB.12.0;


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 2:22 PM
  • Ok, that worked in a C# program I had to fix, but not in the VB code.

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;DataSource=D:\S\Job.mdb")

            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S\Job.dbf", AccessConn)

            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    Here is the error

    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

    Tuesday, July 17, 2012 2:54 PM
  • I think it's the 32-bit vs. 64-bit issue. There is an ACE OLEDB Provider for each version. If your Office 2010 install is 32-bit then the Platform option of the project should be x86. If your Office 2010 install is 64-bit, then a Platform option of Any CPU or x64 should be selected.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 3:10 PM
  • I do not see that option in the Express version. 
    Tuesday, July 17, 2012 4:03 PM
  • I did confirm we have Office 2010 64 bit installed.
    Tuesday, July 17, 2012 4:04 PM
  • I do not see that option in the Express version. 

    Tools...Settings...then select Expert Settings. Then, Tools...Options...Projects and Solutions (General) and select the Show advanced build configurations option. After these options have been set you should be able to select Build...Configuration Manager... and then select the Platform.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 5:08 PM
  • The platform says x86, and that is all it shows. It is not showing the other options. However on a different projects I see the anyCPU option.

    So why would one project show the x86, x64, and any CPU, while another only shows x86?

    Anyone else experience this problem?

    Glenn

    Tuesday, July 17, 2012 7:45 PM
  • Ok, I created anew CPU thing, and then was able to choose AnyCPU. But I get this error.

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147467259
      Message=Could not find installable ISAM.
      Source=Microsoft Access Database Engine
      StackTrace:
           at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
           at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
           at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
           at System.Data.OleDb.OleDbConnection.Open()
           at WindowsApplication1.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication1\Form1.vb:line 6
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Tuesday, July 17, 2012 7:50 PM
  • The platform says x86, and that is all it shows. It is not showing the other options. However on a different projects I see the anyCPU option.

    So why would one project show the x86, x64, and any CPU, while another only shows x86?

    Have you upgraded the project from VB6 or did you create a new VB.Net solution? In the former case, the wizard probably has created an x86 config only.

    Armin

    Tuesday, July 17, 2012 7:57 PM
  • Armin, you are correct it was upgraded from VB6, but I have drastically changed the program trying to get off the VB6 language to 2010. So that makes since now why it said x86 only. I do have it on anycpu now. 

    Now to solve the latest error. Message=Could not find installable ISAM.

    Glenn 

    Tuesday, July 17, 2012 8:17 PM
  • OK, this doesn't make sense to me. If you are running 64-bit Windows with 64-bit Office installed and the project Platform is Any CPU then your app is running 64-bit. If it's running 64-bit, then it will attempt to load the 64-bit ACE OLEDB, which is installed with 64-bit Office.

    You indicated that a C# app works fine with the ACE OLEDB Provider. What is the Platform setting for that project?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 17, 2012 9:50 PM
  • The C# is set to anyCPU just like the VB project. 

    Are there any references I need to add?

    Glenn

    Wednesday, July 18, 2012 2:26 PM
  • Just saw the same problem in another thread: Look at the connection string. It's  "data source", not "datasource". So simple sometimes. :-)

    Armin

    Wednesday, July 18, 2012 2:38 PM
  • Just saw the same problem in another thread: Look at the connection string. It's  "data source", not "datasource". So simple sometimes. :-)

    Armin


    You know I thought about checking that yesterday, right in front of my face, but I just assumed he copied my connection as is and then modified the "data source"...but not to "datasource". ;-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 18, 2012 2:46 PM
  • I did copy, at least I was pretty sure I did. But New error. So making progress. 

    System.InvalidOperationException was unhandled

      Message=The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
      Source=System.Data
      StackTrace:
           at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
           at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
           at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
           at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
           at System.Data.OleDb.OleDbConnection.Open()
           at WindowsApplication2.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication2\Form1.vb:line 6
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Wednesday, July 18, 2012 3:06 PM
  • I fixed this one be repairing the ace engine.

    Glenn

    Wednesday, July 18, 2012 3:10 PM
  • Ok so on line AccessCommand.ExecuteNonQuery(), I get the following error.

    System.InvalidOperationException was unhandled
      Message=ExecuteNonQuery: Connection property has not been initialized.
      Source=System.Data
      StackTrace:
           at System.Data.OleDb.OleDbCommand.ValidateConnection(String method)
           at System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method)
           at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
           at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
           at WindowsApplication2.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication2\Form1.vb:line 9
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Wednesday, July 18, 2012 3:12 PM
  • Could you please post your current code? I don't remember it anymore? :) Also specify the error line (line 9).

    Armin

    Wednesday, July 18, 2012 3:15 PM
  • Sure. Below is the code. 

     Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\S\Job.mdb")

            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S\Job.dbf")

            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    Glenn

    Wednesday, July 18, 2012 3:27 PM
  • Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S\Job.dbf", AccessConn)

    There was no Connection object specified when the Command was created.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Wednesday, July 18, 2012 3:30 PM
  • Sure. Below is the code. 

     Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\S\Job.mdb")

            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S\Job.dbf")

            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    Glenn

    As per Paul, you would need provide a connection to the command object.

            Dim AccessCommand As New System.Data.OleDb.OleDbCommand( _
                "SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S\Job.dbf", _
                AccessConn)


    KSG

    Wednesday, July 18, 2012 3:46 PM
  • On the "AccessCommand.ExecuteNonQuery()" line I now get this error Syntax error in FROM clause.

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147217900
      Message=Syntax error in FROM clause.
      Source=Microsoft Access Database Engine
      StackTrace:
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
           at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
           at WindowsApplication2.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication2\Form1.vb:line 11
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Wednesday, July 18, 2012 3:55 PM
  • Now I am getting a different error, "Characters found after end of SQL statement."

     AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand( _
                "SELECT * INTO [Job] FROM dBase III;DATABASE=D:\S\Job.dbf", AccessConn)
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147217900
      Message=Characters found after end of SQL statement.
      Source=Microsoft Access Database Engine
      StackTrace:
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
           at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
           at WindowsApplication2.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication2\Form1.vb:line 9
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Wednesday, July 18, 2012 5:02 PM
  • I think you have to keep the database name in square brackets. Maybe the SQL syntax is not correct. Have a look here:

    IN Clause (Microsoft Access SQL)



    Armin

    Wednesday, July 18, 2012 5:25 PM
  • Ok I am getting really close now. I get this error, "The Microsoft Access database engine cannot find the input table or query 'Jobx'. Make sure it exists and that its name is spelled correctly."

    Here is the code I am trying, I changed my job.dbf to jobx.dbf just to have it easier identified.:

     Dim AccessConn As New System.Data.OleDb.OleDbConnection( _
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\S\Job.mdb")

            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand( _
                "SELECT * INTO [Job] FROM [Jobx] [dBase III DATABASE=D:\S;]", AccessConn)
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    I also tried   "SELECT * INTO [Job] FROM [Jobx] [dBase III DATABASE=D:\S\;]", AccessConn) 'Same error as above

    And I tried:

    "SELECT * INTO [Job] FROM [Jobx] [dBase III DATABASE=D:\S\Jobx.dbf;]", AccessConn)

    Which gives me the error, "Invalid bracketing of name '[dBase III DATABASE=D:\S\JobX.dbf;]'."

    Yes the JobX.dbf does exist. So I know it is still a syntax thing.

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147217865
      Message=The Microsoft Access database engine cannot find the input table or query 'Jobx'. Make sure it exists and that its name is spelled correctly.
      Source=Microsoft Access Database Engine
      StackTrace:
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
           at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
           at WindowsApplication2.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\Glenn\AppData\Local\Temporary Projects\WindowsApplication2\Form1.vb:line 10
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    The detail error.

    Wednesday, July 18, 2012 6:14 PM
  • I don't see the "IN" clause in your query. That what the link is about. Have a look at the syntax again.

    Armin

    Wednesday, July 18, 2012 6:30 PM
  • Sorry about the confusion. Below is the syntax from the original statement I posted (modified for your table names). Give it a try:

    SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S].[JobX]


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 18, 2012 6:40 PM
  • Excellent that worked perfectly. and I was able to reverse it to have access export to a DBF file.

    I am thinking something like: "SELECT * INTO [dBase III;DATABASE=D:\S].[JobIX] FROM [Job]", AccessConn)

    Thank you to all who helped, now I get to add all this to a program using Arrays I created. W0000t. 

    Glenn. 

    Wednesday, July 18, 2012 7:07 PM