从ODBC连接到MS Access数据库中选择数据 [英] Select data from an ODBC connection into a MS Access database

问题描述

首先要有一些背景知识.在我工作的地方，我们访问编程工具的机会有限.我们可以访问Microsoft Office Suite，因此即使有更好的解决方案，我们的大多数项目也是在Access中创建的.我们最近获得了对Visual Studio 2013的访问权限，我有兴趣将一些更常用的工具转换为VB.NET项目.

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.

使用VBA多年后，我对VBA有了很好的了解，但是，转换为VB.NET绝对是一个变化，尽管我了解它的概念，但是我过去使用的许多功能都不存在在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.

哪个使我想到了以下问题.

Which leads me to the following question.

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

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?

这是我当前的代码.

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

很显然，这是行不通的.我已经基于Internet上的示例尝试了一些方法，但是无法拼凑任何有效的代码.

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.

在使用Access数据库时，我只需链接到ODBC连接和后端Access数据库中的表，然后可以使用DoCmd运行SQL以根据需要移动数据，但是对于VB.NET，我没有那种奢侈.也许由于我缺乏对Visual Studio的了解，这一切都是错的.

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.

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

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.

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

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.

推荐答案

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

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.

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

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

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

，我们可以通过名为"myDb"的ODBC DSN到达该SQL Server实例.我们可以通过Access查询将该表引用为

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

[ODBC;DSN=myDb].[product]

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

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

并从名为[product]的SQL Server表中拉出产品名称，我们可以在VB.NET中做到这一点:

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)
    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

可打印

3 pound(s) of bacon

这篇关于从ODBC连接到MS Access数据库中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！