Excel通过VBA导出到Access导致不稳定 [英] Excel Exporting to Access, via VBA, is Causing Instability

问题描述

我必须在Excel中创建170多个命名范围，我试图将它们加载到Access表中。下面是我的代码。

  Sub Load_To_ALLL_TSD（）
 
 Dim strDatabasePath as String 
 Dim oApp作为Access.Application 
 Dim PathOfworkbook作为字符串
 
 PathToDB = ThisWorkbook.Path 
 strDatabasePath = PathToDB&  \RAROC.accdb 
 
 Set oApp = CreateObject（ Access.Application）
'Set db = Application.CurrentProject 
 oApp.Visible = True 
 
 oApp.OpenCurrentDatabase strDatabasePath 
 
设置db = CurrentDb（）
设置rs = db.OpenRecordset（ ALLL_TSD，dbOpenTable）
 
和oApp 
与rs 
 .AddNew'创建新记录
'向记录
中的每个字段添加值。Fields（ TSD_Base_Rate_Received）= Range（ TSD_Base_Rate_Received）。Value 
 .Fields（ TSD_Base_Rate_Received_Input）=范围（ TSD_Base_Rate_Received_Input）。Value 
 .Fields（ TSD_Calculated_RAROC）= Range（ TSD_Calculated_RAROC）。Value 
 .Fields（ TSD_Capital_F = Range（ TSD_Capital_Factor）。Value 
 
'等，等等，还有更多字段和命名范围
 
'如有必要，添加更多字段... 
 。更新商店新记录
结束于
结束于
 
 Set oApp = Nothing 
 MsgBox（完成！所有数据都保存到RAROC数据库中！！）
 
 End Sub 
  

I 我遇到了一些奇怪的错误！如果我使用F8运行代码，则可以正常工作。如果我单击按钮来触发代码，则有时它可以工作，有时却不起作用。我在几行中都出错了。 / p>

一旦在这里抛出错误：

 设置rs = db。 OpenRecordset（ ALLL_TSD，dbOpenTable）
  

错误读取对象变量或未设置块 p>

一旦它说 Microsoft Access停止工作，并在此行上引发了错误。

.Fields（值

我也看到了其他一些奇怪的东西。

我对这两者都有一个引用：

  Microsoft DAO 3.6对象库
 Microsoft Access 14.0对象库
  

好像我是establis建立与Access的连接，然后几乎立即以某种方式丢失了连接。

最后，我没有窗体或报表，并且数据库没有拆分。我现在只想写一张桌子。

有人可以帮我吗？

谢谢！

解决方案

这是一个不使用Access的基本示例。

需要引用 Microsoft ActiveX数据对象2.x库

 子测试器（）
 
 Dim con作为New ADODB.Connection，rs作为New ADODB.Recordset 
 
 con.Open Provider = Microsoft.ACE.OLEDB.12.0; _ 
& 数据源=&此工作簿。  \RAROC.accdb 
 
'获取一个空的记录集，将新记录添加到
 rs.Open中，选择 * from [ALLL_TSD] where false，con，_ 
 adOpenDynamic，adLockBatchOptimistic 
 
和rs 
 .AddNew 
 .Fields（ TSD_Base_Rate_Received）= Range（ TSD_Base_Rate_Received）。值
 .Fields（ TSD_Base_Rate_Received_Input） =范围（ TSD_Base_Rate_Received_Input）。值
 .Fields（ TSD_Calculated_RAROC）。范围（ TSD_Calculated_RAROC）。值
 .Fields（ TSD_Capital_Factor）=范围（ TSD_Capital_Factor）。值
'等... 
 .UpdateBatch'<<编辑
。关闭
以
 
 con结尾。关闭
结束Sub 
  

I have to create over 170 named ranges in Excel which I am trying to load into an Access table. Below is my code.

Sub Load_To_ALLL_TSD()

Dim strDatabasePath As String
Dim oApp As Access.Application
Dim PathOfworkbook As String

PathToDB = ThisWorkbook.Path
strDatabasePath = PathToDB & "\RAROC.accdb"

Set oApp = CreateObject("Access.Application")
'Set db = Application.CurrentProject
oApp.Visible = True

oApp.OpenCurrentDatabase strDatabasePath

Set db = CurrentDb()
Set rs = db.OpenRecordset("ALLL_TSD", dbOpenTable)

    With oApp
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("TSD_Base_Rate_Received") = Range("TSD_Base_Rate_Received").Value
                .Fields("TSD_Base_Rate_Received_Input") = Range("TSD_Base_Rate_Received_Input").Value
                .Fields("TSD_Calculated_RAROC") = Range("TSD_Calculated_RAROC").Value
                .Fields("TSD_Capital_Factor") = Range("TSD_Capital_Factor").Value 

                ' etc, etc, lot more fields and named ranges here

                ' add more fields if necessary...
                .Update ' stores the new record
            End With
    End With

Set oApp = Nothing
MsgBox ("Done!  All Data saved to RAROC database!!")

End Sub

I'm getting some weird errors! If I run the code using F8, it works fine. If I click a button to fire the code, sometimes it works and sometimes it doesn't work. I has errored out on several different lines.

Once it threw an error here:

Set rs = db.OpenRecordset("ALLL_TSD", dbOpenTable)

Error reads 'object variable or with block not set'

Once it said 'Microsoft Access has stopped working' and it threw an error on this line.

.Fields("TSD_Base_Rate_Received_Input") = Range("TSD_Base_Rate_Received_Input").Value

I've seen some other weird things too.

I have a reference set to both:

Microsoft DAO 3.6 Object Library
Microsoft Access 14.0 Object Library

It almost seems like I'm establishing a connection to Access and then almost immediately I lost the connection, somehow.

Finally, I have no Forms or Reports, and the DB is not split. I have just one single table in there now, which I am trying to write to.

Can someone help me out here?

Thanks!

解决方案

Here's a basic example without using Access.

Needs a reference to Microsoft ActiveX Data Objects 2.x Library

Sub Tester()

    Dim con As New ADODB.Connection, rs As New ADODB.Recordset

    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
             & "Data Source = " & ThisWorkbook.Path & "\RAROC.accdb"

    'get an empty recordset to add new records to
    rs.Open "select * from [ALLL_TSD] where false", con, _
             adOpenDynamic, adLockBatchOptimistic

    With rs
        .AddNew
        .Fields("TSD_Base_Rate_Received") = Range("TSD_Base_Rate_Received").Value
        .Fields("TSD_Base_Rate_Received_Input") = Range("TSD_Base_Rate_Received_Input").Value
        .Fields("TSD_Calculated_RAROC") = Range("TSD_Calculated_RAROC").Value
        .Fields("TSD_Capital_Factor") = Range("TSD_Capital_Factor").Value
        'etc...
        .UpdateBatch '<< EDIT
        .Close
    End With

    con.Close
End Sub

这篇关于Excel通过VBA导出到Access导致不稳定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！