将Access数据库查询复制到Excel电子表格中 [英] Copy Access database query into Excel spreadsheet

问题描述

我有一个Access数据库和一个Excel工作簿。
我需要做的是查询数据库并将查询粘贴到工作表中。

问题是Runtime。我已经完成了程序，一切正常，但是工作速度非常慢，我们每次查询的时间长达30秒，尽管大部分运行时间都是通过 CopyFromRecordset 调用。 / p>

数据库在查询表中有超过800k行。
目前在我公司，每天早上都有人手动查询表并将其复制并粘贴到Excel中。我正在尝试删除这个过程。

这是我所拥有的：

  Sub new1（）
 
 Dim objAdoCon As Object 
 Dim objRcdSet As Object 
 
'获取查询信息'
 Dim DataArr（）
表格（Data2）。激活
 DataArr = Range（A1：B40）
 
对于i = 1 To UBound（DataArr）
 
 job = DataArr（i，1）
 dest = DataArr（i，2）
 
如果InStr（dest，HT）> 0然后
 OpCode =3863
 ElseIf InStr（dest，HIP）> 0然后
 OpCode =35DM
 End If 
 
 strQry =SELECT * from [BATCHNO] WHERE（[BATCHNO]。[Job] ='& job& ;'）AND（[BATCHNO]。[OperationCode] =&& OpCode&'）
 
设置objAdoCon = CreateObject（ADODB.Connection）
设置objRcdSet = CreateObject（ADODB.Recordset）
 
 objAdoCon.OpenProvider = Microsoft.Jet.oledb.4.0;数据源= C：\Users\v-adamsje\ Desktop \HTMS.mdb
'长运行时间
 objRcdSet.Open strQry，objAdoCon 
'很长的运行时间
 ThisWorkbook.Worksheets（dest）.Range（A2 ）.CopyFromRecordset objRcdSet 
 
设置objAdoCon = Nothing 
设置objRcdSet =没有
 
下一个i 
 
 End Sub 
  

任何帮助都不胜感激。我是VBA和Access的新手，所以这可能是一个简单的修复。谢谢

解决方案

Excel非常适合自己获取数据，而不使用VBA。

在DATA功能区上

1. 创建一个表的连接或数据视图（例如mdb或SServer ）




2. 然后使用现有连接按钮将连接的表中的数据添加到工作表表（ListObject）。




3. 您甚至可以将工作簿（即连接）设置为每12小时刷新数据。

对于您需要抓取数据的所有表/视图重复此操作。你甚至可以指定SQL作为连接的一部分。

让excel照顾自己。

我刚刚抓住来自附近磁盘的25万行表。2 0秒。

它会照顾自己，没有维护的代码！

I have an Access database and an Excel workbook. What I need to do is query the database and paste the query into a worksheet.

The issue is Runtime. I have stepped throught the program and everything works, but it works extremely slow, we're talking up to 30 second run times per query, although most of this run time is coming with the CopyFromRecordset call.

The database has over 800k rows in the table I'm querying. Currently at my company there are people every morning who manually query the tables and copy and paste them into excel. I'm trying to remove this process.

Here is what I have:

Sub new1()

    Dim objAdoCon As Object
    Dim objRcdSet As Object

    ' gets query information '
    Dim DataArr()
    Sheets("Data2").Activate
    DataArr = Range("A1:B40")

    For i = 1 To UBound(DataArr)

        job = DataArr(i, 1)
        dest = DataArr(i, 2)

        If InStr(dest, "HT") > 0 Then
            OpCode = "3863"
        ElseIf InStr(dest, "HIP") > 0 Then
            OpCode = "35DM"
       End If

       strQry = "SELECT * from [BATCHNO] WHERE ([BATCHNO].[Job]='" & job & "') AND ([BATCHNO].[OperationCode] = " & "'" & OpCode & "')"

       Set objAdoCon = CreateObject("ADODB.Connection")
       Set objRcdSet = CreateObject("ADODB.Recordset")

       objAdoCon.Open "Provider = Microsoft.Jet.oledb.4.0;Data Source = C:\Users\v-adamsje\Desktop\HTmaster.mdb"
       'long run time
       objRcdSet.Open strQry, objAdoCon
       'very long run time
       ThisWorkbook.Worksheets(dest).Range("A2").CopyFromRecordset objRcdSet

      Set objAdoCon = Nothing
      Set objRcdSet = Nothing

   Next i

End Sub

Any help is appreciated. I am new to VBA and Access so this could be an easy fix. Thanks

解决方案

Excel is very good at getting data for itself, without using VBA.

On the DATA ribbon

1. create a connection to a table or view of data somewhere (eg mdb or SServer)

2. then use the "existing connections" button to add data from your connected table to a worksheet table (ListObject).

3. You can even set the workbook (ie connection) to refresh the data every 12 hours.

Repeat for all the tables /view you need to grab data for. You can even specify SQL as part of the connection.

Let excel look after itself.

I just grabbed a 250,000 row table from a "nearby" disk in 2 secs.

It will look after itself and has no code to maintain!

这篇关于将Access数据库查询复制到Excel电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！