时间戳

首页 > 解决方案 > Excel VBA AccessApplication.OpenCurrentDatabase Not Working

excel - Excel VBA AccessApplication.OpenCurrentDatabase Not Working

问题描述

Here is what I'm trying to do with Excel VBA:

  1. Copy a range in Excel
  2. Open an Access database
  3. Delete records from the CV table
  4. Paste the new records from Excel into the CV table
  5. Run a make table query
  6. Close the database

The code below worked - once. After it ran successfully once, it will not run again. There is no error message - the Access DB just never opens and the macro ends. Nothing ran behind the scenes, the Access DB was never touched.

I am speculating that the error might have to do with the fact that the application was opened once and maybe not closed properly and therefore can't reopen? (No idea if this is accurate/makes sense)

Sheets("NAHVCV").Select
Range("A:C").Select
Selection.Copy

Dim appAccess As New Access.Application

Set appAccess = Access.Application

appAccess.OpenCurrentDatabase AccessDBPath
appAccess.Visible = True

appAccess.CurrentDb.Execute "DELETE * FROM [CV]"
appAccess.DoCmd.OpenTable "CV", acViewNormal, acEdit
appAccess.DoCmd.RunCommand acCmdPasteAppend

appAccess.DoCmd.Close acTable, "CV", acSaveYes

appAccess.DoCmd.OpenQuery "qryMakFutRetroVariance"

appAccess.CloseCurrentDatabase

appAccess.Quit acQuitSaveAll

标签： excelvbams-access

解决方案

也许在子上方/外部定义访问应用程序：

将 appAccess 调暗为新的 Access.Application

Sub Test() '添加代码的其余部分 End Sub

我遇到了与您类似的问题，但是一旦我将访问应用程序移出子系统，我的访问文件每次都会打开

推荐阅读