create MS access DB from VB
-
hi I'm trying to write a code that create DB & tables in MS access using VB, when I excute my code the DB is created but I don't see any table in the DB when I open the access file. Can any one tell me what's the problem? This is my code: Public Function CreateAccessDatabase(ByVal DatabaseFullPath As String) As Boolean Dim bAns As Boolean Dim cat As New ADOX.Catalog() Dim table1 As New ADOX.Table() Dim cn As ADODB.Connection cn = New ADODB.Connection Try Dim sCreateString As String sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath cat.Create(sCreateString) bAns = True Catch Excep As System.Runtime.InteropServices.COMException bAns = False 'Open the connection cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\testdb.mdb") 'Open the Catalog cat.ActiveConnection = cn 'Create the table table1.Name = "Test_Table" 'Create and Append a new field to the "Test_Table" 'Columns Collection table1.Columns.Append("PrimaryKey_Field", ADOX.DataTypeEnum.adInteger) 'Create and Append a new key. Note that we are merely passing 'the "PimaryKey_Field" column as the source of the primary key. 'Thi snew Key will be Appended to the Keys Collection of '"Test_Table" table1.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "PrimaryKey_Field") 'Append the newly created table to the Tables Collection cat.Tables.Append(table1) ' clean up objects table1 = Nothing cat = Nothing cn.Close() cn = Nothing Finally End Try Return bAns End Function
-
hi I'm trying to write a code that create DB & tables in MS access using VB, when I excute my code the DB is created but I don't see any table in the DB when I open the access file. Can any one tell me what's the problem? This is my code: Public Function CreateAccessDatabase(ByVal DatabaseFullPath As String) As Boolean Dim bAns As Boolean Dim cat As New ADOX.Catalog() Dim table1 As New ADOX.Table() Dim cn As ADODB.Connection cn = New ADODB.Connection Try Dim sCreateString As String sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath cat.Create(sCreateString) bAns = True Catch Excep As System.Runtime.InteropServices.COMException bAns = False 'Open the connection cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\testdb.mdb") 'Open the Catalog cat.ActiveConnection = cn 'Create the table table1.Name = "Test_Table" 'Create and Append a new field to the "Test_Table" 'Columns Collection table1.Columns.Append("PrimaryKey_Field", ADOX.DataTypeEnum.adInteger) 'Create and Append a new key. Note that we are merely passing 'the "PimaryKey_Field" column as the source of the primary key. 'Thi snew Key will be Appended to the Keys Collection of '"Test_Table" table1.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "PrimaryKey_Field") 'Append the newly created table to the Tables Collection cat.Tables.Append(table1) ' clean up objects table1 = Nothing cat = Nothing cn.Close() cn = Nothing Finally End Try Return bAns End Function
Fromi what I've seen, this should work. The only problem I can see is that you are specifying the connection string twice, built by two seperate methods. This is one method:
mesho wrote:
Dim sCreateString As String sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath cat.Create(sCreateString)
which takes a database file path as a parameter. The other speicifies a very specific filepath, which could be very different from the above path:
mesho wrote:
'Open the connection cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\testdb.mdb") 'Open the Catalog cat.ActiveConnection = cn
This is probably where you're probliem lies. You're looking in one file for the table, but the table was created in another file.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007