Update Access Database from SQL

[stevo_300]

Hi All,

I currently have a SQL database. I am trying to populate a Microsoft Access (.mdb) database dynamically with specific info.

I have got a connection to both databases, however when I try to update the access database it says that the Insert Into Syntax is incorrect. This would suggest to me that the Command Builder isn't doing its job.

Please could you take a look and possibly suggest a way to get the code to update the database?

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data
Imports System.IO
Imports System.IO.Path
Imports System.Math

Public Class mappoint

    Dim SQLDA As SqlDataAdapter
    Dim SQLDS As DataSet
    Dim con As SqlConnection = New SqlConnection("working connection String")
    Dim sql As String = "SELECT * FROM Table"

    Dim ADB As AccessDataSource = New AccessDataSource("~\App_Data\mappoint.mdb", "SELECT * FROM Customer")

    Sub Fill()

        con.Open()
        SQLDA = New SqlDataAdapter(sql, con)
        SQLDS = New DataSet()
        SQLDA.Fill(SQLDS, "cust")
        con.Close()

    End Sub



    Sub initialSetup()
        Fill()
        Dim conn As New OleDb.OleDbConnection
        Dim prov, source As String
        prov = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        source = "Data Source = C:\map\App_Data\mappoint1.mdb"
        conn.ConnectionString = prov & source
        conn.Open()

        Dim ADA As OleDb.OleDbDataAdapter
        Dim ADS As New DataSet
        Dim sql1 As String = "SELECT * FROM Customer"
        ADA = New OleDb.OleDbDataAdapter(sql1, conn)
        ADA.Fill(ADS, "maps")

        Dim newrow As DataRow
        Dim cb As OleDb.OleDbCommandBuilder

        Dim row As Integer = SQLDS.Tables("cust").Rows.Count - 1
        For i = 1 To row
            cb = New OleDb.OleDbCommandBuilder(ADA)
            newrow = ADS.Tables("maps").NewRow()
            newrow.Item("Customer Number") = CStr(SQLDS.Tables("cust").Rows(i).Item("No_"))
            newrow.Item("Customer Name") = CStr(SQLDS.Tables("cust").Rows(i).Item("Name"))
            newrow.Item("Address1") = CStr(SQLDS.Tables("cust").Rows(i).Item("Address"))
            newrow.Item("Address2") = CStr(SQLDS.Tables("cust").Rows(i).Item("Address 2"))
            newrow.Item("City") = CStr(SQLDS.Tables("cust").Rows(i).Item("City"))
            newrow.Item("County") = CStr(SQLDS.Tables("cust").Rows(i).Item("County"))
            newrow.Item("PostCode") = CStr(SQLDS.Tables("cust").Rows(i).Item("Post Code"))
            newrow.Item("Phone Number") = CStr(SQLDS.Tables("cust").Rows(i).Item("Phone No_"))
            newrow.Item("SalesPerson") = CStr(SQLDS.Tables("cust").Rows(i).Item("Salesperson Code"))
            ADS.Tables("maps").Rows.Add(newrow)
            ADA.Update(ADS, "maps")
        Next

        conn.Close()
        ADA = Nothing
        ADS = Nothing
        SQLDA = Nothing
        SQLDS = Nothing

    End Sub
    

End Class

Kind Regards

Steve

 

[stevo_300]

I have managed to overcome this problem by hardcoding the SQL String (Using Concatanation) and then using the following code:

Dim ADA As OleDb.OleDbDataAdapter
ADA.UpdateCommand = connection.CreateCommand
ADA.UpdateCommand.CommandText = SQLSTATEMENT As String
ADA.UpdateCommand.ExecuteNonQuery()

 

[MrDavidjenkins]

ADA error

When I look at your code and past the ADA into the project, I get the following with declaration expected errors.

I got to admit I am using your work as a go by.

Thanks


Dim ADA As OleDb.OleDbDataAdapter
ADA.UpdateCommand = connection.CreateCommand
ADA.UpdateCommand.CommandText = SQLSTATEMENT As String
ADA.UpdateCommand.ExecuteNonQuery()
Dim SQLDA As SqlDataAdapter
Dim SQLDS As DataSet
Dim con As SqlConnection = New SqlConnection("working connection String")
Dim sql As String = "SELECT * FROM Table"

Dim ADB As AccessDataSource = New AccessDataSource("~\App_Data\mappoint.mdb", "SELECT * FROM Customer")

 

[cjard]

Follow the DW4 link in my signature, section "creating a simple data app" - this is how Microsoft say you should do your data access