Accessing APPX Data From VB Using AppxODBC

The Appx ODBC client allows you to access APPXIO data from external applications such as Excel, Crystal Reports, and other ODBC compliant applications. These applications will format the appropriate SQL command and the selected data will be returned. Products such as Visual Basic do not all have a clear user interface for the user to build the necessary command. The following code is provided by Ed Griffith as an example of using VB coding to execute a SQL command from VB to access APPXIO data. This is just a model and will have to be modified to fit your own needs.

The following code is a sample for accessing the ODBC connection and
extracting data.

I am using this to populate a SQLCE database for use on a mobile handheld PC
with a scanner.

This code reads the APPX Vendor file and then loads the data into a SQLCE
database table called Vendor for use on the handheld PC.


        Dim userName As String = Environment.UserName

   ' Build a path to the sqlce database you are placing the info into
     It could just as easily be another database or text file.

        Dim dbPath = "C:\Documents and Settings\" & userName & "\My
Documents\Pocket_PC My Documents\DataStructures\HF-Receive.sdf"

        Dim ConnStr As String = "Data Source = " & dbPath
        Dim VendorID, VendorName, PONo As String
        Dim LineNo As Integer
        Dim ItemDesc, PartNo As String
        Dim ItemQty As Integer
        Dim IntCount, IntCount2 As Integer
        Dim CmdQrYstr As String
        Dim conn As New SqlCeConnection(ConnStr)
        Dim SQLCEDB As New SqlCeEngine(ConnStr)

Dim cmdOdbc As OdbcCommand
        Dim dr As OdbcDataReader

        Dim strSQL As String

        Dim oODBCConnection As OdbcConnection

        Dim sConnString As String = _
        "Dsn=appxodbc;" & _
        "database=appx;" & _
        "pwd=password;" & _
        "Uid=username"

        Try
            oODBCConnection = New Odbc.OdbcConnection(sConnString)

            Dim cmd As New SqlCeCommand

' Use the APPXODBC file naming convention for your sql statement
            strSQL = "SELECT VENDOR_NO,NAME FROM C01_MAP_VENDOR"

            oODBCConnection.Open()
            cmdOdbc = New OdbcCommand(strSQL, oODBCConnection)

            dr = cmdOdbc.ExecuteReader
            cmd.Connection = conn ' Sets which connection you want to use
            cmd.CommandType = CommandType.Text ' Sets the command type to
use
            conn.Open() ' Open the connection if it fails

            If dr.HasRows Then
                Do While dr.Read()

                    VendorID = Convert.ToString(dr("vendor_no"))
                    VendorName = Convert.ToString(dr("Name"))

            ' check to see if the record exists
                    cmd.CommandText = "SELECT COUNT (*) FROM VENDOR where
VENDORID='" + VendorID + "'"
                    IntCount = cmd.ExecuteScalar()

            ' Add if it's a new record
                    If IntCount = 0 Then
                        cmd.CommandText = "INSERT INTO VENDOR (VENDORid,
VENDORNAME) VALUES ('" + VendorID + "','" + VendorName + "')"
                        cmd.ExecuteScalar()
                    End If
                Loop
            End If

            dr.Close()

Comments:

Read what other users have said about this page or add your own comments.

-- GaryRogers - 2010-12-30