Use ODBC DSN's to Access / Manipulate Data

**cathalo** · 03-30-2009 10:01 AM

Hey Community,

I have done some vb.net courses in school, however none with database or getting into inheritance etc. I specialized in System Admin.

I now have the need to create an application that extracts data from our ERP software. The software uses CISAM files and we have Transoft ODBC connections available to the data.

I'm lost on how to access this data. I'm using MS VB 2008 Express edition. I recently downloaded and installed the references found here (How to use the ODBC .NET Managed Provider in Visual Basic .NET and connection strings) on how to make an ODBC connection.

I have created a button that does the following successfully, which displays the first record in a row - I terminate the app to stop it.

Code:

 Dim conn As OdbcConnection
        Dim connectionString As String
        Dim comm As OdbcCommand
        Dim dr As OdbcDataReader
        Dim sqlStr As String

        connectionString = "dsn=Encore50A;uid=;pwd=;"
        conn = New OdbcConnection(connectionString)
        sqlStr = "Select * from INVPRICE"

        conn.Open()
        comm = New OdbcCommand(sqlStr, conn)
        dr = comm.ExecuteReader()

        While (dr.Read())
            MsgBox(dr.GetValue(0).ToString())
        End While


        conn.Close()
        dr.Close()
        comm.Dispose()
        conn.Dispose()
    End Sub

There are few things I guess I need:

a) A good resource with examples so I can understand how this works
b) Showi me how to display the data in a DataGrid - with/without the ability to modify.
c) Figure out how (via programming) to copy tables from this odbc connection to a local MDB file.

Feel free to lead me to water, I can attempt to drink and come back with more questions

**cathalo** · 03-31-2009 8:49 AM

Good to see the message has been read, no replies.

I've progressed a little - I have found some code (however I don't completely understand it) that creates an Access DB.

Code:

Public Function CreateAccessDatabase( _
    ByVal DatabaseFullPath As String) As Boolean
        Dim bAns As Boolean
        Dim cat As New ADOX.Catalog()
        Try

            'Make sure the folder
            'provided in the path exists. If file name w/o path 
            'is  specified,  the database will be created in your
            'application folder.

            Dim sCreateString As String
            sCreateString = _
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               DatabaseFullPath
            cat.Create(sCreateString)

            bAns = True

        Catch Excep As System.Runtime.InteropServices.COMException
            bAns = False
            'do whatever else you need to do here, log, 
            'msgbox etc.
        Finally
            cat = Nothing
        End Try
        Return bAns
    End Function

I have also found a few sample "CopyTable" examples - but receive various errors from these.

I will keep digging.

**cathalo** · 03-31-2009 9:54 AM

I receive this message when attempting the code listed below.
Any ideas on this one?

ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function

Code:

Dim cn As New OdbcConnection("dsn=Encore50A;uid=;pwd=;")
        Dim cmd As OdbcCommand
        Dim adp As OdbcDataAdapter
        Dim ds As New DataSet

        cn.Open()
        cmd = New OdbcCommand("select * from INVPRICE", cn)
        adp = New OdbcDataAdapter(cmd)
        adp.Fill(ds, "StockCode")

        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "StockCode"
        'MsgBox(cn.State.ToString)
        cn.Close()

**jin29_neci** · 03-31-2009 2:34 PM

try to configure your database file at ODBC Data Source Administrator that can be found at control panel>Aministrative Tools>Data Sources....

**cjard** · 04-06-2009 5:15 AM

Originally Posted by cathalo

a) A good resource with examples so I can understand how this works

Not much to understand at this stage; youre opening a database conenction and reading from a database
If you want a tabular representation of your data, you'd be better using a DataAdapter and a DataTable. the adapters Fills the table

b) Showi me how to display the data in a DataGrid - with/without the ability to modify.

Modify depends on the driver ability to write; we don't know if that exists, but get cracking on with a relevant commandbuilder (OdbcCommandBuilder) to amke the Insert/Update/Delete Queries for you, or write your own, and use the DataAdapter Update() method to save changes

Showing the data in a DataGridView (not DataGrid; old component) is a matter of setting the grid's DataSource to be the filled DataTable

c) Figure out how (via programming) to copy tables from this odbc connection to a local MDB file.

You'd be better off reading the DW2 link in my signature, section Creating a Simple Data App, to give you a rought overwiew of datasets, datatables, etc

As for copying the tables intoa local db, you may find it better to have the jet driver do that directly

see connectionstrings.com if it has an entry for inline attachment to another database.. Jet supports some syntax something like

SELECT * INTO new_table FROM [dsn=Encore50A;uid=;pwd=;]

i.e. the jet (access) driver will connect the dsn, retrieve the data and copy it locally. Hopefully; there may be no need for your app to proxy it

**SysVic** · 06-05-2009 12:01 AM

cathalo,

I take it you are trying to connect to Syspro using ODBC's.

Send me a message when you get this if you are still interestered.

Cheers

**cathalo** · 06-11-2009 3:36 PM

Yes,

Not having success. Seems to be multiple methods - however can't get any of them to work.

Thread: Use ODBC DSN's to Access / Manipulate Data

LinkBack

Thread Tools

Search Thread

Display

Use ODBC DSN's to Access / Manipulate Data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions