none
Error connecting to access database from VS 2017 RRS feed

  • Question

  • I am having an issue connecting to an access database to upload some data from SQL in the access database. This process was working before but we have migrated to a new server and the process is no longer working.

    Here is my code:

     
           Dim g_objCn As New ADODB.Connection
           Dim g_strConnectionString As String = "C:\database\data.accdb"
           Dim connsql = New SqlConnection
    
            connsql.ConnectionString = "integrated security=SSPI;data source=sqlserver;" &
                                        "persist security info=False;initial catalog=MainTables;MultipleActiveResultSets=true;"
            Try
                connsql.Open()
                g_objCn.Provider = "Microsoft.ACE.OLEDB.16.0"
                g_objCn.Properties("Data Source").Value = g_strConnectionString
                g_objCn.Open()
                g_objCn.Execute("INSERT INTO [C:\database\data.accdb].tblData (id, FacN, LName,FName)" &
                                "SELECT id, FacN, LName,FName " &
                "FROM [tblData] IN '' [ODBC;Driver={SQL Server};Server=sqlserver;Database=MainTables;Trusted_Connection=yes]")
    

    I am getting the error below on the line g_objCn.Provider = "Microsoft.Ace.oledb.16.0". I have checked the new server and that library is there (aceoledb.dll). So I am not sure what I am missing.

    Tuesday, June 4, 2019 1:11 PM

All replies

  • The message is saying that g_objCn is not open. You need to add code to catch errors and to show relevant messages and other data when there is an error. You should check to see if g_objCn is open before using it.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, June 4, 2019 9:08 PM
  • Hi J-Bal,

    Do you have any updates? If this issue is not solved, please feel free to let us know.

    If you have already solved, we are welcome you share the solution or mark the helpful reply in here, that will also help other community members, thanks for your understanding.

    Best regards,

    Sara


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, June 7, 2019 8:47 AM
    Moderator