同步两个数据库的对象的程序MS Access

问题描述：

我正在使用ADODB在VB 2005中开发一个程序。考虑两个MS Access数据库。一个用table1，另一个用table2，table3。同步两个数据库的对象的程序MS Access

当我们运行程序并选择两个数据库。它将创建第二个数据库的table2，table3到第一个数据库。要产生

该方案可以成功地产生的表的列表。之后，我有另一个功能来创建“CREATE TABLE”语句。

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后，错误是相同的。我想要的只是关于create table语句的帮助。下面是Table2的图像，其中create table语句是。从我的分贝 Table2 image

做了一些测试。无法使Access接受UNSIGNED BYTE类型。 – June7

也不喜欢长，短，二进制，位，单，双，货币后的parens（）。根本不喜欢Decimal。 – June7

谢谢你们！你们俩。有效。但现在我陷入了另一个问题。完成一天的辛勤工作后，结果告诉我查询创建的列没有设置很多属性。所以，我必须选择另一种方法来使用vb 2005在ms access db中创建表格。任何建议！ –

答

代码在后台和编辑表属性创建表：

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 & "Data\LabData.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 & "Data\LabData.accdb" 
    CurrentDb.TableDefs.Append tdf 

    Set rs = New ADODB.Recordset 
    rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic 
    Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.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

答

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创建的表之间存在一些差异。

同步两个数据库的对象的程序MS Access

相关推荐