Randy Toye Randy Toye - 1 year ago 80
Vb.net Question

Select data from an ODBC connection into a MS Access database

A little background first. Where I work we have limited access to programming tools. We have access to the Microsoft Office Suite and therefore most of our projects are created in Access even though there are better solutions out there. We recently received access to Visual Studio 2013 and I am interested in converting some of our more heavily used tools into VB.NET projects.

I have a good understanding of VBA after using it for so many years, however, converting to VB.NET is definitely a change and although I understand the concept of it, many of the functions I used in the past do not exist in VB.NET.

Which leads me to the following question.

How do I connect to one database, an ODBC connection, then put selected fields from a table in that database to a table in a Microsoft Access database?

Here is my current code.

Imports System.Data.Odbc
Imports System.Data.Odbc.OdbcCommand
Imports System.Data.OleDb

Public Class Form1

Dim conn As OdbcConnection
Dim connBE As OleDb.OleDbConnection

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
Call Connect_SLICWave()
Call Connect_Backend()

Dim beCmd As New OleDb.OleDbCommand(sqlInsert, connBE)

End Sub

Private Sub Connect_SLICWave()
Dim connectionString As String

connectionString = "Dsn=slic_wave;uid=userid;pwd=password"
conn = New OdbcConnection(connectionString)
End Sub

Private Sub Connect_Backend()
Dim connectionStringBE As String

connectionStringBE = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database.accdb"
connBE = New OleDb.OleDbConnection(connectionStringBE)
End Sub

End Class

Clearly this is not going to work. I have tried a few things based on examples on the Internet but have been unable to piece together any kind of code that works.

When using the Access database I would simply link to the tables in both the ODBC connection and the backend Access database and then I could use DoCmd to run SQL to move data as needed, however with VB.NET I don't have that luxury. Perhaps I am going about this all wrong due to my lack of knowledge with Visual Studio.

Is there a better way to accomplish my end goal? I need to be able to refer to the data in the ODBC connection and then store it somewhere so that I can output a specific dataset to the end user. Can/should I use a DataSet or DataTable? How much data can be stored in a DataSet/DataTable before the program would become unstable? The data used in this process can be quite excessive at times.

Typically the user would send the tool some criteria with 4 or 5 fields worth of data. The tool will then turn around and take that criteria to get the proper dataset from the ODBC connected database using joins on about 5 to 7 tables and returns one set of data to the user. Yes, it is a bit excessive, but that's the requirement.

I hope I am explaining this well enough without being too generic. The nature of my business prevents providing specific examples.

Sorry for being longwinded and I appreciate any effort that goes into helping me solve this issue. If there is anything that needs to be clarified please let me know and I will try to explain it more clearly.

Answer Source

You may find it helpful to be aware that when you run a query against the Access Database Engine from a .NET application you can use ODBC references in your queries and the engine will perform the required ODBC connections for you. In effect, these are temporary "on the fly" ODBC linked tables created for that specific query.

Say we have a table named [product] in SQL Server

id  name
--  -----
 1  bacon
 2  tofu

and we can reach that SQL Server instance via an ODBC DSN named "myDb". We can reference that table from an Access query as


So, for example, if we want to query an Access table named [Orders]

OrderID  ProductID  Qty  Units  OrderDate
-------  ---------  ---  -----  ----------
      1          1    3  pound  2016-10-17

and pull in the product names from the SQL Server table named [product] we can do this in VB.NET:

Dim myConnectionString As String =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=C:\Users\Public\Database1.accdb;"
Using conn As New OleDbConnection(myConnectionString)
    Dim sql As String =
        "SELECT p.name, o.Qty, o.Units " +
        "FROM " +
            "Orders o " +
            "INNER JOIN " +
            "[ODBC;DSN=myDb].[product] p " +
                "ON p.id = o.ProductID"
    Using cmd As New OleDbCommand(sql, conn)
        Using rdr As OleDbDataReader = cmd.ExecuteReader
            While rdr.Read
                Console.WriteLine("{0} {1}(s) of {2} ", rdr("Qty"), rdr("Units"), rdr("name"))
            End While
        End Using
    End Using
End Using

which prints

3 pound(s) of bacon
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download