vb.net 从 ODBC 连接中选择数据到 MS Access 数据库

Select data from an ODBC connection into a MS Access database

提问by Randy Toye

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.

先介绍一下背景。在我工作的地方，我们对编程工具的访问有限。我们可以访问 Microsoft Office 套件，因此我们的大部分项目都是在 Access 中创建的，即使那里有更好的解决方案。我们最近获得了对 Visual Studio 2013 的访问权限，我有兴趣将一些使用频率更高的工具转换为 VB.NET 项目。

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.

用了这么多年对VBA有了很好的了解，但是转换到VB.NET肯定是一个变化，虽然我理解了它的概念，但我过去使用的许多功能在VB中是不存在的。网。

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?

如何连接到一个数据库，即 ODBC 连接，然后将该数据库表中的选定字段放入 Microsoft Access 数据库中的表中？

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 sqlInsert As String = "INSERT INTO tblUOCs (EIAC,LCN,ALC,UOC) SELECT DISTINCT Trim(EIACODXA),Trim(LSACONXB),Trim(ALTLCNXB),Trim(UOCSEIXC) FROM ALAV_XC"
        Dim beCmd As New OleDb.OleDbCommand(sqlInsert, connBE)

        beCmd.ExecuteNonQuery()
    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.

显然这是行不通的。我已经根据 Internet 上的示例尝试了一些方法，但一直无法拼凑出任何有效的代码。

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.

使用 Access 数据库时，我只需链接到 ODBC 连接和后端 Access 数据库中的表，然后我可以使用 DoCmd 运行 SQL 以根据需要移动数据，但是使用 VB.NET 我没有那么奢侈。也许由于我对 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.

有没有更好的方法来实现我的最终目标？我需要能够引用 ODBC 连接中的数据，然后将其存储在某处，以便我可以向最终用户输出特定的数据集。我可以/应该使用 DataSet 或 DataTable 吗？在程序变得不稳定之前，DataSet/DataTable 中可以存储多少数据？此过程中使用的数据有时可能会非常多。

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.

通常，用户会向工具发送一些标准，其中包含 4 或 5 个字段的数据。然后，该工具将转向并采用该标准，使用大约 5 到 7 个表的连接从 ODBC 连接的数据库中获取正确的数据集，并将一组数据返回给用户。是的，这有点过分，但这就是要求。

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.

抱歉我啰嗦了，我感谢为帮助我解决这个问题所做的任何努力。如果有什么需要澄清的，请告诉我，我会尽量解释得更清楚。

采纳答案by Gord Thompson

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.

当您从 .NET 应用程序对 Access 数据库引擎运行查询时，您可能会发现它很有帮助，您可以在查询中使用 ODBC 引用，引擎将为您执行所需的 ODBC 连接。实际上，这些是为该特定查询创建的临时“动态”ODBC 链接表。

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

假设我们在 SQL Server 中有一个名为 [product] 的表

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

我们可以通过名为“myDb”的 ODBC DSN 访问该 SQL Server 实例。我们可以从 Access 查询中引用该表作为

[ODBC;DSN=myDb].[product]

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

因此，例如，如果我们要查询名为 [Orders] 的 Access 表

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:

并从名为 [product] 的 SQL Server 表中提取产品名称，我们可以在 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)
    conn.Open()
    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

回答by Joel Coehoorn

First of all, I need to ask about the original source for your SLICWaveODBC connection. Is it still in Access, or are you perhaps pulling from Sql Server or similar at this point? ODBC is going to pass your command statement on to the original source, and if you're using Sql Server now, instead of Access, some of the SQL syntax will change on you.

首先，我需要询问您的SLICWaveODBC 连接的原始来源。它是否仍在 Access 中，或者您此时是否从 Sql Server 或类似的东西中提取？ODBC 会将您的命令语句传递给原始源，并且如果您现在使用的是 Sql Server，而不是 Access，则某些 SQL 语法将在您身上发生变化。

For the remainder of the question, I'll assume the SQL you have will work if executed. If it turns out you need help converting that to T-SQL for SQL Server, open a separate question limited to that specific problem.

对于问题的其余部分，我将假设您拥有的 SQL 将在执行时起作用。如果结果证明您需要帮助将其转换为 SQL Server 的 T-SQL，请打开一个仅限于该特定问题的单独问题。

That out of the way, I'm now going to limit my scope to this statement:

顺便说一句，我现在将我的范围限制在这个声明中：

I need to be able to refer to the data in the ODBC connection and ... output a specific dataset to the end user.

我需要能够引用 ODBC 连接中的数据并...向最终用户输出特定的数据集。

What you want to do is put a DataGridViewcontrol on your form (I'll use the default DataGridView1name for now). Then make the form code look like this:

您想要做的是DataGridView在表单上放置一个控件（我现在将使用默认DataGridView1名称）。然后使表单代码如下所示：

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

Public Class Form1

    Private Property SLICWaveConnectionString As String = "Dsn=slic_wave;uid=userid;pwd=password" 

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim sql As String = "SELECT DISTINCT Trim(EIACODXA),Trim(LSACONXB),Trim(ALTLCNXB),Trim(UOCSEIXC) FROM ALAV_XC"
        Dim dt As New DataTable
        Using cn  As New OleDb.OleDbConnection(SLICWaveConnectionString), _
              cmd As New OleDb.OleDbCommand(sql, cn)
              da  As New OleDb.OleDbDataAdapter(cmd)

              da.Fill(dt)
        End Using
        DataGridView1.DataSource = dt
    End Sub

End Class