我有一个程序,可以删除Access2010数据库中任何超过特定日期的数据,然后压缩数据库。程序的删除部分运行良好,但当我尝试压缩它时,会出现错误"无效参数"。以下是我的代码:
'Deleting anything older than chosen before databse is compacted.
Dim DateA As Date = Date.Now
Dim DateB As String
Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:ForteFortedb.accdb"
Dim ParentCNN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:ForteFortedb.accdb"
Dim CloneCNN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:ForteTemp.accdb"
Dim cnn As New OleDbConnection(ConnString)
Dim sql As String
'Formatting the date to make it 7 days into the past.
DateB = Format(DateA.AddDays(ApplicationPropertiesWindow.DeleteFilebox.SelectedIndex + 1), "MM/dd/yy")
cnn.Open()
'Delete everything from b_forte where proddate is less than date - chosen time.
sql = "DELETE * FROM b_forte WHERE ProdDate < #" & DateB & "#"
Dim Command = New OleDb.OleDbCommand(sql, cnn)
Command.ExecuteNonQuery()
cnn.Close()
'Compacting the databse
Try
Dim JrO As New JRO.JetEngine
cnn.Open()
JrO.CompactDatabase(ParentCNN, CloneCNN)
If System.IO.File.Exists("C:ForteTemp.accdb") Then
System.IO.File.Delete("C:ForteFortedb.accdb")
Rename("C:ForteTemp.accdb", "C:ForteFortedb.accdb")
Logging("Database compacted.")
cnn.Close()
End If
Catch ex As Exception
MainTextBox.AppendText(Environment.NewLine & "Database Compression Failure :" & vbCr & ex.Message)
End Try
我使用的是vb.net 2010,访问2010时数据库上没有密码。
CompactDatabase
的参数只是路径和文件名,而不是连接信息。
BTWVB.NET不区分大小写,所以我不会重复使用JRO:
Dim JrO As New JRO.JetEngine
它可能会按原样工作,但以后读起来会很困惑,而且可能会在某个阶段引发冲突。
我从@Andy G那里发现这是来自CompactDatabase
的连接信息。我必须更改代码中的信息是:CloneCNN
。字符串为"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:ForteTemp.accdb"
,为了修复无效参数错误消息,我在CloneCNN
的末尾添加了;Jet OLEDB:Engine Type=5
。