一个程序，用于同步两个数据库MS Access的对象

Question

我正在使用ADODB在VB 2005中开发一个程序。 考虑两个 MS 访问数据库。一个带有表 1，另一个带有表 2、表 3。

当我们运行程序并选择两个数据库时。它将第二个数据库的表2，表3创建到第一个数据库中。

界面图像

程序可以成功生成要生成的表的列表。之后，我有另一个函数来创建"创建表"语句。

Function CreateCreateTableStatement(ByVal DBPath As String, ByVal TableName As String) As String
On Error GoTo EndErr
Dim cnn As New ADODB.Connection
Dim TablesSchema, ColumnsSchema, PrimaryKeysSchema As ADODB.Recordset
Dim tempsql, PrimaryKeyColumn As String
Dim i As Integer = 0
cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBPath & "';"
cnn.Mode = ADODB.ConnectModeEnum.adModeShareExclusive
DoLog("Getting tables list of " & DBPath)
cnn.Open()
TablesSchema = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
TablesSchema.Filter = "TABLE_NAME = '" & TableName & "'"
PrimaryKeysSchema = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaPrimaryKeys)
PrimaryKeysSchema.Filter = "TABLE_NAME = '" & TableName & "'"
If PrimaryKeysSchema.EOF = False Then PrimaryKeyColumn = PrimaryKeysSchema("COLUMN_NAME").Value
PrimaryKeysSchema.Close()
ColumnsSchema = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaColumns)
ColumnsSchema.Filter = "TABLE_NAME = '" & TableName & "'"
tempsql = "CREATE TABLE " & TableName & " ("
Do While Not ColumnsSchema.EOF
tempsql = tempsql + ColumnsSchema("COLUMN_NAME").Value & " " & DataCodeToName(ColumnsSchema("DATA_TYPE").Value) & " " & " (" & ColumnsSchema("CHARACTER_MAXIMUM_LENGTH").Value & ") " '& ColumnsSchema("IS_NULLABLE").Value & ColumnsSchema("COLUMN_DEFAULT").Value & ", " & ColumnsSchema("IS_NULLABLE").Value & ", " & DataCodeToName(ColumnsSchema("DATA_TYPE").Value) & ", " & ColumnsSchema("CHARACTER_MAXIMUM_LENGTH").Value
If PrimaryKeyColumn = ColumnsSchema("COLUMN_NAME").Value Then tempsql = tempsql + " PRIMARY KEY, " Else tempsql = tempsql + ", "
ColumnsSchema.MoveNext()
Loop
tempsql = tempsql.Substring(0, Len(tempsql) - 2) + ");"
cnn.Close()
DoLog("Gotten tables list of " & DBPath)
Return tempsql
Exit Function
EndErr:
cnn.Close()
MsgBox(Err.Description)
End Function

函数的输出是在更改它之前：

CREATE TABLE Table2 (Column1 VARCHAR, Column11 UNSIGNED BYTE, Column12 SHORT, Column13 SINGLE, Column14 DOUBLE, Column15 Guid, Column16 NUMERIC, Column2 VARCHAR, Column3 LONG, Column4 DateTime, Column5 CURRENCY, Column6 LONG PRIMARY KEY, Column7 BIT, Column8 BINARY, Column9 VARCHAR);

但是在执行 SQL 后，引发了错误"字段定义中的语法错误"。所以我稍微改变了函数并添加了最大长度，如上面的代码所示。上面的代码生成了 SQL：

CREATE TABLE Table2 (Column1 VARCHAR  (255) , Column11 UNSIGNED BYTE  () , Column12 SHORT  () , Column13 SINGLE  () , Column14 DOUBLE  () , Column15 LONG  () , Column16 DECIMAL  () , Column2 VARCHAR  (0) , Column3 LONG  () , Column4 DateTime  () , Column5 CURRENCY  () , Column6 LONG  ()  PRIMARY KEY, Column7 BIT  (2) , Column8 BINARY  (0) , Column9 VARCHAR  (0) );

生成上述SQL后，错误是相同的。 我想要的只是关于创建表语句的帮助。下面是 Table2 的图像，其中的创建表语句就是其中的。 表2 图片

Answer 1

我的数据库中的代码用于在后端创建表并编辑表属性：

If IsNull(Me.tbxTestNum) Then
MsgBox "Must enter test number.", vbCritical, "Error"
Else
Set cn = New ADODB.Connection
'connect to the backend database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "DataLabData.accdb'"
'create the test table
cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
'set table link
Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
tdf.SourceTableName = Me.tbxTestNum
tdf.Connect = "; DATABASE=" & gstrBasePath & "DataLabData.accdb"
CurrentDb.TableDefs.Append tdf
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
Set db = DBEngine.OpenDatabase(gstrBasePath & "DataLabData.accdb")
While Not rs.EOF
If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
'create field in new table
cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
End If
'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
If rs!DataType = "Text" Then
'change the AllowZeroLength default Yes to No
db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
End If
rs.MoveNext
Wend
rs.Close
cn.Close
db.Close
End If

Answer 2

Function CreateCreateTableStatement(ByVal DBPath As String, ByVal TableName As String) As String
'CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2]
' [, …]] [, CONSTRAINT multifieldindex [, …]])
On Error GoTo EndErr
Dim cnn As New ADODB.Connection
Dim TablesSchema, ColumnsSchema, PrimaryKeysSchema As ADODB.Recordset
Dim tempsql, PrimaryKeyColumn, ColLen As String
Dim i As Integer
cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBPath & "';"
cnn.Mode = ADODB.ConnectModeEnum.adModeShareExclusive
DoLog("Getting tables list of " & DBPath)
cnn.Open()
TablesSchema = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
TablesSchema.Filter = "TABLE_NAME = '" & TableName & "'"
PrimaryKeysSchema = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaPrimaryKeys)
PrimaryKeysSchema.Filter = "TABLE_NAME = '" & TableName & "'"
If PrimaryKeysSchema.EOF = False Then PrimaryKeyColumn = PrimaryKeysSchema("COLUMN_NAME").Value
PrimaryKeysSchema.Close()
ColumnsSchema = cnn.OpenSchema(ADODB.SchemaEnum.adSchemaColumns)
ColumnsSchema.Filter = "TABLE_NAME = '" & TableName & "'"
'ColumnsSchema.Sort = "`ORDINAL_POSITION`"
tempsql = "CREATE TABLE `" & TableName & "` ("
Do While Not ColumnsSchema.EOF
If ColumnsSchema("CHARACTER_MAXIMUM_LENGTH").Value.ToString = "" Or ColumnsSchema("CHARACTER_MAXIMUM_LENGTH").Value.ToString = "0" Then ColLen = "" 'Else ColLen = "(" & ColumnsSchema("CHARACTER_MAXIMUM_LENGTH").Value & ")"
tempsql = tempsql & "`" & ColumnsSchema("COLUMN_NAME").Value & "` " & DataCodeToName(ColumnsSchema("DATA_TYPE").Value) & " " & ColLen ' & ColumnsSchema("IS_NULLABLE").Value & ColumnsSchema("COLUMN_DEFAULT").Value & ", " & ColumnsSchema("IS_NULLABLE").Value & ", " & DataCodeToName(ColumnsSchema("DATA_TYPE").Value) & ", " & ColumnsSchema("CHARACTER_MAXIMUM_LENGTH").Value
If PrimaryKeyColumn = ColumnsSchema("COLUMN_NAME").Value Then tempsql = tempsql + " NOT NULL IDENTITY PRIMARY KEY, " Else tempsql = tempsql + ", "
ColumnsSchema.MoveNext()
Loop
tempsql = tempsql.Substring(0, Len(tempsql) - 2) + ");"
cnn.Close()
DoLog("Gotten tables list of " & DBPath)
Return tempsql
Exit Function
EndErr:
cnn.Close()
MsgBox(Err.Description)
End Function

谢谢你们。但是今天我发现了创建表的正确 SQL。

CREATE TABLE `Table3` (`Column1` VARCHAR , `Column11` BYTE , `Column12` SHORT , `Column13` SINGLE , `Column14` DOUBLE , `Column15` GUID , `Column16` DECIMAL , `Column2` VARCHAR , `Column3` LONG , `Column4` DateTime , `Column5` CURRENCY , `Column6` LONG  NOT NULL IDENTITY PRIMARY KEY, `Column7` BIT , `Column8` OLEOBJECT , `Column9` VARCHAR );

一切正常，但是上述SQL创建的表与通过MS Access Interface创建的表之间存在一些差异。