从SQL查询数据到MS Access:本地表与直通表 [英] Query data from SQL to MS Access: Local Tables vs Pass-Through Tables

问题描述

我创建了一个应用程序，该应用程序使用以下逻辑从SQL查询数据到我的MS Access App.

I've created an application that uses the following logic to query data from SQL to my MS Access App.

1. 使用ODBC连接执行存储过程
2. 使用此功能作为通过查询在本地提取数据.

它看起来像这样:

strSQL = "EXEC StoredProcedure " & Variable & "

Call ChangeQueryDef("qryPassThrough", strSQL)
Call SQLPassThrough(strQDFName:="qryPassThrough", _
      strSQL:=strSQL, strConnect:=gODBCConn)

Me.frmDataSheet.Form.RecordSource = "qryPassThrough"

但是，最近，我们已使用高可用性故障转移系统将SQL Server升级到2016年-因此我们的连接字符串已更改为可以连接到侦听器，如下所示:

But, recently we have upgraded our SQL Server to 2016 using a high availability failover system - hence our connection string has changed to connect to a listener like so:

gODBCConn = "ODBC;Driver= {SQL Server Native Client 11.0};Trusted_Connection=Yes;Regional=Yes;Database=" & varDB & ";MultiSubnetFailover=Yes;IntegratedSecurity=SSPI;Server=tcp:SERVER_LISTENER,1433;"

但是，似乎在连接字符串中使用SQL Server Native Client与我们最初使用的SQL Server不同.某些数据类型已更改，无法在Access中使用.

However, it looks like using SQL Server Native Client in the connection string is not the same as what we originally had which was SQL Server. Certain data types have changed and do not work in Access.

我是否有更好的方法从SQL查询数据并在使用ADO或其他方法的访问中持久保存/显示此数据?

Is there a better way for me to query data from SQL and persist/display this data in access using ADO or an alternative method?

编辑基于评论:

• 我遇到的问题是我在SQL中使用数据类型为Decimal(12,2)的表.经过一些测试和试验，使用ODBC直通查询时，这似乎失败了.但是将数据类型更改为Float似乎可以正常工作.然后还有其他似乎也出错的数据类型，而我还没有找到.似乎有一些我不知道的差异，并且我渴望找到一种更好的方式将数据加载到Access App中.

编辑2 这是我得到的与数据类型问题有关的错误消息.

EDIT 2 This is the error message I get relating to the data type issue.

推荐答案

听起来您对使基础数据结构与Access兼容并不十分感兴趣，所以:

Sounds like you're not really interested in making the underlying data structure compatible with Access, so:

创建表单

首先，创建一个数据表表单.对于此示例，我们将命名为frmDynDS表单.用256个文本框(名称为Text0到Text255)填充表单.要用文本框填充表单，可以在表单处于设计视图时使用以下帮助函数:

First, create a datasheet form. For this example, we're going to name our form frmDynDS. Populate the form with 256 text boxes, named Text0 to Text255. To populate the form with the text boxes, you can use the following helper function while the form is in design view:

Public Sub DynDsPopulateControls()
    Dim i As Long
    Dim myCtl As Control
    For i = 0 To 255
        Set myCtl = Application.CreateControl("frmDynDS", acTextBox, acDetail)
        myCtl.NAME = "Text" & i
    Next i
End Sub

VBA将记录集绑定到表单

首先，我们将允许表单自身进行引用，以使其持久化:

First, we're going to allow the form to persist, by allowing it to reference itself:

(所有代码均在frmDynDS的代码模块中)

(all on in the code module for frmDynDS)

Public Myself As Object

然后，我们将添加VBA以使其加载记录集.我正在使用Object而不是ADODB.Recordset来允许它同时使用DAO和ADODB记录集.

Then, we're going to add VBA to make it load a recordset. I'm using Object instead of ADODB.Recordset to allow it to both take DAO and ADODB recordsets.

Public Sub LoadRS(myRS As Object)
    Dim i As Long
    Dim myTextbox As textbox
    Dim fld As Object
    i = 0
    With myRS
        For Each fld In myRS.Fields
            Set myTextbox = Me.Controls("Text" & i)
            myTextbox.Properties("DatasheetCaption").Value = fld.NAME
            myTextbox.ControlSource = fld.NAME
            myTextbox.ColumnHidden = False
            i = i + 1
        Next fld
    End With
    For i = i To 255
        Set myTextbox = Me.Controls("Text" & i)
        myTextbox.ColumnHidden = True
    Next i
    Set Me.Recordset = myRS
End Sub

使用表格

(全部在使用frmDynDS的表单模块中)

(all in the module of the form using frmDynDS)

1. 作为独立的数据表表格

1. As an independent datasheet form

Dim frmDS As New Form_frmDynDS
frmDS.Caption = "My ADO Recordset"
frmDS.LoadRS MyAdoRS 'Where MyAdoRS is an open ADODB recordset
Set frmDS.Myself = frmDS
frmDS.Visible = True
frmDS.SetFocus

请注意，您可以打开此表单的多个实例，每个实例绑定到不同的记录集.

Note that you're allowed to have multiple instances of this form open, each bound to different recordsets.

作为子表单(不绑定子表单控件)

As a subform (leave the subform control unbound)

Me.MySubformControl.SourceObject = "frmDynDS"
Me.MySubformControl.Form.LoadRS MyAdoRS 'Where MyAdoRS is an open ADODB recordset

警告:Access在排序和过滤数据表表单时使用命令文本.如果它包含Access的语法错误(因为它是T-SQL)，则在尝试排序/过滤时会出现错误.但是，如果语法有效，但是无法执行SQL(例如，由于您使用的参数不再可用)，则Access将会严重崩溃，丢失所有未保存的更改，并可能破坏数据库.即使您禁用排序/筛选，尝试排序时仍然可以触发硬崩溃.您可以在SQL中使用注释使语法无效，从而避免这些崩溃.

Warning: Access uses the command text when sorting and filtering the datasheet form. If it contains a syntax error for Access (because it's T-SQL), you will get an error when trying to sort/filter. However, when the syntax is valid, but the SQL can't be executed (for example, because you're using parameters, which are no longer available), then Access will hard crash, losing any unsaved changes and possibly corrupting your database. Even if you disable sorting/filtering, you can still trigger the hard crash when attempting to sort. You can use comments in your SQL to invalidate the syntax, avoiding these crashes.

这篇关于从SQL查询数据到MS Access:本地表与直通表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！