Creating Access database with VB6
-
I am creating a database in Access with 3 tables. Each table has different number of columns or fields. I get an error when creating more than 8 fields. If I comment out the other fields, then everything works great. Can anyone help? Code is as follows: Dim FileName As String, strConn As String Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table Dim tblCollection As Collection Dim Script As Object 'check and/or build DB Set Script = CreateObject("Scripting.filesystemobject") If Script.FolderExists("C:\Business Pro") Then 'folder is in place...move on If Dir("C:\Business Pro\BusinessPro.mdb") = "" Then 'dir not in place...build lblPB.Caption = "No database found. Creating database..." FileName = "C:\Business Pro\BusinessPro.mdb" 'create instance of ADOX-object Set adoCat = New ADOX.Catalog 'creat instance of Collection-object Set tblCollection = New Collection Set adoTable = New ADOX.Table strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FileName & ";" 'create the db adoCat.Create (strConn) 'name table tblCollection.Add "Customer_Contacts" With adoTable .Name = "Customer_Contacts" 'name the table .Columns.Append "CustID", adInteger 'create a field in primary key .ParentCatalog = adoCat 'set Parent catalog .Columns("CustID").Properties("AutoIncrement").Value = True .Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key 'add rest of fields .Columns.Append "FirstName", adWChar .Columns.Append "LastName", adWChar .Columns.Append "Address", adWChar .Columns.Append "City", adWChar .Columns.Append "State", adWChar .Columns.Append "Zip", adLongVarWChar, 5 .Columns.Append "PhoneNumber", adLongVarWChar, 10 End With 'add table to mdb adoCat.Tables.Append adoTable 'create 2nd table Set adoTable = New ADOX.Table 'name table tblCollection.Add "Labor_Rates" With adoTable .Name = "Labor_Rates" 'name the table .Columns.Append "ID", adInteger 'create a field in primary key .ParentCatalog = adoCat 'set Parent catalo
-
I am creating a database in Access with 3 tables. Each table has different number of columns or fields. I get an error when creating more than 8 fields. If I comment out the other fields, then everything works great. Can anyone help? Code is as follows: Dim FileName As String, strConn As String Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table Dim tblCollection As Collection Dim Script As Object 'check and/or build DB Set Script = CreateObject("Scripting.filesystemobject") If Script.FolderExists("C:\Business Pro") Then 'folder is in place...move on If Dir("C:\Business Pro\BusinessPro.mdb") = "" Then 'dir not in place...build lblPB.Caption = "No database found. Creating database..." FileName = "C:\Business Pro\BusinessPro.mdb" 'create instance of ADOX-object Set adoCat = New ADOX.Catalog 'creat instance of Collection-object Set tblCollection = New Collection Set adoTable = New ADOX.Table strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FileName & ";" 'create the db adoCat.Create (strConn) 'name table tblCollection.Add "Customer_Contacts" With adoTable .Name = "Customer_Contacts" 'name the table .Columns.Append "CustID", adInteger 'create a field in primary key .ParentCatalog = adoCat 'set Parent catalog .Columns("CustID").Properties("AutoIncrement").Value = True .Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key 'add rest of fields .Columns.Append "FirstName", adWChar .Columns.Append "LastName", adWChar .Columns.Append "Address", adWChar .Columns.Append "City", adWChar .Columns.Append "State", adWChar .Columns.Append "Zip", adLongVarWChar, 5 .Columns.Append "PhoneNumber", adLongVarWChar, 10 End With 'add table to mdb adoCat.Tables.Append adoTable 'create 2nd table Set adoTable = New ADOX.Table 'name table tblCollection.Add "Labor_Rates" With adoTable .Name = "Labor_Rates" 'name the table .Columns.Append "ID", adInteger 'create a field in primary key .ParentCatalog = adoCat 'set Parent catalo
I'm afraid you are really pushing your luck trying to get support for a language that has not been supported for more than a decade. You should download and use the free VB.net express.
Never underestimate the power of human stupidity RAH
-
I'm afraid you are really pushing your luck trying to get support for a language that has not been supported for more than a decade. You should download and use the free VB.net express.
Never underestimate the power of human stupidity RAH
I was just thinking of learning the .Net language and converting over and rewrite my whole program. I guess now is as good a time as any to do it. Thanks!
-
I was just thinking of learning the .Net language and converting over and rewrite my whole program. I guess now is as good a time as any to do it. Thanks!
I would recommend converting to c#, the learning curve will be marginally steeper but the learning resources are 2-3 time the amount of VB.net code.
Never underestimate the power of human stupidity RAH
-
I am creating a database in Access with 3 tables. Each table has different number of columns or fields. I get an error when creating more than 8 fields. If I comment out the other fields, then everything works great. Can anyone help? Code is as follows: Dim FileName As String, strConn As String Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table Dim tblCollection As Collection Dim Script As Object 'check and/or build DB Set Script = CreateObject("Scripting.filesystemobject") If Script.FolderExists("C:\Business Pro") Then 'folder is in place...move on If Dir("C:\Business Pro\BusinessPro.mdb") = "" Then 'dir not in place...build lblPB.Caption = "No database found. Creating database..." FileName = "C:\Business Pro\BusinessPro.mdb" 'create instance of ADOX-object Set adoCat = New ADOX.Catalog 'creat instance of Collection-object Set tblCollection = New Collection Set adoTable = New ADOX.Table strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FileName & ";" 'create the db adoCat.Create (strConn) 'name table tblCollection.Add "Customer_Contacts" With adoTable .Name = "Customer_Contacts" 'name the table .Columns.Append "CustID", adInteger 'create a field in primary key .ParentCatalog = adoCat 'set Parent catalog .Columns("CustID").Properties("AutoIncrement").Value = True .Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key 'add rest of fields .Columns.Append "FirstName", adWChar .Columns.Append "LastName", adWChar .Columns.Append "Address", adWChar .Columns.Append "City", adWChar .Columns.Append "State", adWChar .Columns.Append "Zip", adLongVarWChar, 5 .Columns.Append "PhoneNumber", adLongVarWChar, 10 End With 'add table to mdb adoCat.Tables.Append adoTable 'create 2nd table Set adoTable = New ADOX.Table 'name table tblCollection.Add "Labor_Rates" With adoTable .Name = "Labor_Rates" 'name the table .Columns.Append "ID", adInteger 'create a field in primary key .ParentCatalog = adoCat 'set Parent catalo
Some points that might help ... You're not defining the maximum size of the text columns so they are all defaulting to 255. Did you really want a Memo field for DaysOnJob? For that matter, shouldn't LaborRate be adDouble not adWChar? Or an integer - linking to Labor_Rates.ID Consider using the DAO library rather than the ADO extension library (which is not stable). On the subject of using VB6 - now is definitely the time to stop using it! But don't assume that VB.NET is the natural progression into .NET. C# is really worth considering. If not then MacDonald publish a book for programmers transitioning from VB6 (The one I had was "The Book of Visual Basic 2005", but that's well out of date now).
-
Some points that might help ... You're not defining the maximum size of the text columns so they are all defaulting to 255. Did you really want a Memo field for DaysOnJob? For that matter, shouldn't LaborRate be adDouble not adWChar? Or an integer - linking to Labor_Rates.ID Consider using the DAO library rather than the ADO extension library (which is not stable). On the subject of using VB6 - now is definitely the time to stop using it! But don't assume that VB.NET is the natural progression into .NET. C# is really worth considering. If not then MacDonald publish a book for programmers transitioning from VB6 (The one I had was "The Book of Visual Basic 2005", but that's well out of date now).
Thanks for the pointers. I went about it all differently. I created the tables I need in Access 2007 and through code, I just copy the database to another directory. I found it to be less of a headache than creating all the tables through code. Thanks again :)
-
Thanks for the pointers. I went about it all differently. I created the tables I need in Access 2007 and through code, I just copy the database to another directory. I found it to be less of a headache than creating all the tables through code. Thanks again :)
Yes - much less of a headache! :) One thing to watch out for .. if in the future you need to change the schema of the database after you have rolled it out to users you will need to consider how to amend existing data... but by then you will have upgraded to .net :laugh:
