首次打开时,使用VB.net创建Access 07 DB始终会运行“修复" [英] Creating Access 07 DB with VB.net always runs Repair when opening it the first time
问题描述
我有一个VB.NET(Visual Studio 2010)应用程序,该应用程序以编程方式创建Access 07数据库,然后将CSV文件导入为新的Access表.创建数据库,并且导入CSV都没有问题.该代码使用ADOX.Catalog创建数据库,并使用带有ACE的OleDb.OleDbConnection导入CSV.一切都很好,除了我第一次打开Access DB.当我从台式机(Office 07)启动Access 07时,屏幕右下方的绿色修复"进度条显示了大约5秒钟.这仅在我第一次打开数据库时发生.数据库和表可以正常工作,但是Access绝对可以解决问题.我可以每次重新创建此行为.第一次打开数据库时如何避免维修?任何想法都会有所帮助.
I have a VB.NET (Visual Studio 2010) app that creates an Access 07 database programmatically, then imports a CSV file as a new Access table. The DB gets created, and the CSV gets imported with no problems. The code uses ADOX.Catalog to create the database, and an OleDb.OleDbConnection with ACE to import the CSV. All is well, except the first time I open the Access DB. When I launch Access 07 from my desktop (Office 07), I get the green "repair" progress bar at the bottom right of the screen for about 5 seconds. It only happens the first time I open the DB. The DB and tables work fine, but Access is definitely fixing something. I can recreate this behavior every time. How can I avoid the repair when opening the DB the first time? Any ideas would be helpful.
Public Function CreateTaxDatabase(ByVal DatabaseFullPath As String) As Boolean
Dim bAns As Boolean
Dim cat As ADOX.Catalog
Try
'' check if file exists
If System.IO.File.Exists(DatabaseFullPath) Then
'' delete the old file
System.IO.File.Delete(DatabaseFullPath)
End If
Dim sCreateString As String
cat = New ADOX.Catalog()
sCreateString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabaseFullPath
cat.Create(sCreateString)
'' close the connection
Dim connection As ADODB.Connection = DirectCast(cat.ActiveConnection, ADODB.Connection)
connection.Close()
bAns = True
Catch Excep As System.Runtime.InteropServices.COMException
bAns = False
Finally
cat = Nothing
End Try
Return bAns
End Function
Sub ImportCSV(dbPath As String, CSVPath As String, CSVFile as String)
Dim conn As OleDb.OleDbConnection = Nothing
Dim SQL As String = ""
Try
conn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbPath)
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error Connecting to Database")
If conn.State = ConnectionState.Open Then
conn.Close()
End If
Return
End Try
SQL = "SELECT * INTO Table1 FROM [Text;FMT=Delimited;HDR=Yes;CharacterSet=ANSI;DATABASE=" + CSVPath + "].[" + CSVFile + "]"
Try
Dim SQLCmd As OleDb.OleDbCommand = conn.CreateCommand
SQLCmd.CommandText = SQL
SQLCmd.ExecuteNonQuery()
Application.DoEvents()
Catch ex As Exception
SQL = "There was an error executing the following SQL Statement:" + vbCrLf + _
SQL + vbCrLf + "Error - " + Trim(Str(Err.Number)) + " " + Err.Description
MsgBox(SQL, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "SQL Error")
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
If Not IsNothing(conn) Then
conn = Nothing
End If
End Try
End Sub
推荐答案
这似乎可以解决问题
Public Sub CompactRepairDB(DBPath As String)
'' compact and repair DB
Dim AccessApp As Microsoft.Office.Interop.Access.Application = New Microsoft.Office.Interop.Access.Application()
Dim TempDB = GetFilePath(DBPath) + "Compact1.ACCDB"
DeleteFile(TempDB)
AccessApp.OpenCurrentDatabase(DBPath)
AccessApp.CloseCurrentDatabase()
AccessApp.CompactRepair(DBPath, TempDB)
AccessApp.Quit()
DeleteFile(DBPath)
'' rename Compact1 to Original DB Name
My.Computer.FileSystem.RenameFile(TempDB, GetFileName(DBPath))
End Sub
这篇关于首次打开时,使用VB.net创建Access 07 DB始终会运行“修复"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!