代码之家 › 专栏 › 技术社区 › Hamees A. Khan

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

adodb ms-access vb.net database

Hamees A. Khan · 技术社区 · 7 年前

我正在用ADODB在VB 2005中开发一个程序。考虑两个MS Access数据库。一个包含表1,另一个包含表2、表3。

该程序可以成功生成要生成的表列表。之后,我有另一个函数来创建“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);

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语句的帮助。下面是表2的图像,其中create table语句是。 Table2 image

2 回复 | 直到 7 年前

June7 7 年前

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

Hamees A. Khan 7 年前

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