Using Access 2007 Queries From VB .NET

Question

I have a access database setup which i am connecting to via ODBC, the access files already has all the required queries built and i would simple like to call them from within my application.

I am using Visual Basic .NET and this is what i have tried so far.....

Thanks in advance for any help you can offer.

Ben

        Dim command As OdbcCommand = New OdbcCommand

    command.Connection = cnx
    command.CommandText = "sp_InsertClient"
    command.CommandType = CommandType.StoredProcedure

    '//====== create ABN paramenter =============
    Dim param1 As OdbcParameter = New OdbcParameter("inABN", abn)
    param1.Direction = ParameterDirection.Input
    param1.DbType = DbType.String
    'add abn parameter
    command.Parameters.Add(param1)

   Dim dataAdapter As OdbcDataAdapter = New OdbcDataAdapter(command)

    dataAdapter.InsertCommand = command
    Try
        cnx.Open()
        dataAdapter.InsertCommand.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    Finally
        cnx.Close()
    End Try


    MessageBox.Show("DONE")

Answer 1

So, what is your question?

Looking at the code above, you shouldn't need a dataAdapter.
You could call command.ExecuteNonQuery() to do the work.

EDIT: Also, you could wrap the OdbcConnection & OdbcCommand in a using...end using block.

Answer 2

Here is the VB Code Public Sub AddClient(ByVal abn As String, ByVal companyName As String, ByVal contactName As String, _ ByVal phoneNumber As String, ByVal address As String)

    'Dim command As OdbcCommand = New OdbcCommand
    Using cnx As New OdbcConnection(My.Settings.connectionString)
        Using command As New OdbcCommand
            command.Connection = cnx
            command.CommandText = "sp_InsertClient"
            command.CommandType = CommandType.StoredProcedure



            '//====== create ABN paramenter =============
            Dim param1 As OdbcParameter = New OdbcParameter("inABN", abn)
            param1.Direction = ParameterDirection.Input
            param1.DbType = DbType.String
            'add abn parameter
            command.Parameters.Add(param1)

            '//====== create CompanyName Parameter ================
            Dim param2 As OdbcParameter = New OdbcParameter("inCompanyName", companyName)
            param2.Direction = ParameterDirection.Input
            param2.DbType = DbType.String
            'add the parameter
            command.Parameters.Add(param2)

            '//======== create ContactName Param ===============
            Dim param3 As OdbcParameter = New OdbcParameter("inContactName", contactName)
            param3.Direction = ParameterDirection.Input
            param3.DbType = DbType.String
            'add the parameter
            command.Parameters.Add(param3)

            '//=========== create PhoneNumber Parameter ===============
            Dim param4 As OdbcParameter = New OdbcParameter("inPhoneNumber", phoneNumber)
            param4.Direction = ParameterDirection.Input
            param4.DbType = DbType.String
            'add paramater
            command.Parameters.Add(param4)

            '//=========== create Address Parameter ===============
            Dim param5 As OdbcParameter = New OdbcParameter("inAddress", address)
            param5.Direction = ParameterDirection.Input
            param5.DbType = DbType.String
            'add paramater
            command.Parameters.Add(param5)

            Try
                cnx.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", _
                                MessageBoxButtons.OK, MessageBoxIcon.Error)

                Exit Sub

            End Try

        End Using

    End Using
    MessageBox.Show("DONE")

End Sub

And here is my query in Access.

INSERT INTO tblClients ( ABN, CompanyName, ContactName, PhoneNumber, Address )

VALUES ([inABN], [inCompanyName], [inContactName], [inPhoneNumber], [inAddress]);

Thanks for all the help...

Answer 3

I have still not managed to get this working,,, can anyone offer any more suggestions?

I have been trawling google for days with no luck :(

Thanks Ben

Answer 4

Edited my old answer... it was wrong

According to this and this, you're doing it right.

An alternative to using prebuilt queryies in access is to define them as Stored Procedures in access. Working with MS Access Stored Procedures in VB.NET. Part 1 is a good overview of this.

Answer 5

Thanks for them links i had seen them already but had missed the adding of EXECUTE, that seems to be getting the queries to run now, but my parameters are not been passed to the access query.

I have tested and my parameters are been added to the command but not to the access query?

I also tried Parameters.AddWithValue("paramname", value)

but this was the same result

Also i changed my query in access to this, turns out the square brackets where not needed...

INSERT INTO tblClients ( ABN, CompanyName, ContactName, PhoneNumber, Address ) VALUES (inABN, inCompanyName, inContactName, inPhoneNumber, inAddress)

Thanks Ben