By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

How to programmatically create a DB and a table and link it to another

Expert 5K+
P: 8,434
Hi all.

Ok, I'm using VB6 but I think the answer to this (if there is one) is more likely to be found in the Access forum.

I have a situation where I've got tens of millions of records, spread over a bunch of separate databases. I don't have a choice about this, as the data is simply too large for a single MDB. As yet, I'm not prepared to go to SQL Server. Possibly in the not-too-distant future, but we'll see. In the meantime, I have this bunch of large-ish databases (say, half a gigabyte each) all with the same single table (identical structure). I then have a front-end database which has all of them linked, and also has a query definition related to each one (to join them up with one or two lookup tables).

I use a VB6 program to read some regularly-generated log files and produce information in a condensed form. I then use a VBA routine to import the info to the latest table, update some stats and so forth. (The stats can be generated on the fly from the data, but I don't usually want to wait a day or two for them).

The problem is that as more data is recorded, I have to manually create the next database, with its empty table, then link it to the front-end, then create the query for it, and so on. I'm looking to automate this, and just for good measure I plan to combine the two steps into a single VB6 program which reads the logs and loads the info directly into the database, instead of producing a text file to be imported.

So, can anyone help me on how, from VB6, I can...
  1. Create an MDB containing an empty table
  2. Link the table to my front-end database, and
  3. (Probably the easy one) create a new query definition based on the linked table?

I'm fine with all the logic behind this, by the way - I can build the application no worries. I just need help on how to achieve these specific tasks. Have tried searching, and the best I found was a hint on how to create a table in an existing database.

I suppose I can achieve task #1 relatively simply by just copying a "stock" MDB and changing the name, but would prefer to create it from scratch. Task #3 is probably not too tough - I expect I can figure that one out with a bit of work. But #2 has me stumped.
Jul 24 '07 #1
Share this Question
Share on Google+
15 Replies


Expert 100+
P: 344
Hi all.

Ok, I'm using VB6 but I think the answer to this (if there is one) is more likely to be found in the Access forum.

I have a situation where I've got tens of millions of records, spread over a bunch of separate databases. I don't have a choice about this, as the data is simply too large for a single MDB. As yet, I'm not prepared to go to SQL Server. Possibly in the not-too-distant future, but we'll see. In the meantime, I have this bunch of large-ish databases (say, half a gigabyte each) all with the same single table (identical structure). I then have a front-end database which has all of them linked, and also has a query definition related to each one (to join them up with one or two lookup tables).

I use a VB6 program to read some regularly-generated log files and produce information in a condensed form. I then use a VBA routine to import the info to the latest table, update some stats and so forth. (The stats can be generated on the fly from the data, but I don't usually want to wait a day or two for them).

The problem is that as more data is recorded, I have to manually create the next database, with its empty table, then link it to the front-end, then create the query for it, and so on. I'm looking to automate this, and just for good measure I plan to combine the two steps into a single VB6 program which reads the logs and loads the info directly into the database, instead of producing a text file to be imported.

So, can anyone help me on how, from VB6, I can...
  1. Create an MDB containing an empty table
  2. Link the table to my front-end database, and
  3. (Probably the easy one) create a new query definition based on the linked table?

I'm fine with all the logic behind this, by the way - I can build the application no worries. I just need help on how to achieve these specific tasks. Have tried searching, and the best I found was a hint on how to create a table in an existing database.

I suppose I can achieve task #1 relatively simply by just copying a "stock" MDB and changing the name, but would prefer to create it from scratch. Task #3 is probably not too tough - I expect I can figure that one out with a bit of work. But #2 has me stumped.
I used to do this all the time, but that was in Access 2 and I no longer have a copy of my solution, nor can I remember how I did it. However, I did a search and this article explains how to do step 2, and provides the code you need.
http://www.alvechurchdata.co.uk/acclink.htm
Jul 24 '07 #2

Expert 5K+
P: 8,434
... this article explains how to do step 2, and provides the code you need.
http://www.alvechurchdata.co.uk/acclink.htm
Thanks, I'll check it out.

One thing I've been wondering is whether I might need to add a reference to the Access object library, then play with Access the way we use Word or Excel. I'm guessing ADO doesn't want to know about the mechanics of Access, since it's a more generic "any database" connection.

Anyway, I'm rambling. I'll go have a look at the link.
Jul 24 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Thanks, I'll check it out.

One thing I've been wondering is whether I might need to add a reference to the Access object library, then play with Access the way we use Word or Excel. I'm guessing ADO doesn't want to know about the mechanics of Access, since it's a more generic "any database" connection.

Anyway, I'm rambling. I'll go have a look at the link.
Hi!

Here is very basic code creating new mdb, adding to it a simple table and linking it to the current db.
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateNewMDB()
  2.  
  3.     Dim appAccess As Access.Application
  4.     Dim tdfNewTable As TableDef
  5.  
  6.     Set appAccess = CreateObject("Access.Application")
  7.  
  8.     With appAccess
  9.         '.Visible = True
  10.         .NewCurrentDatabase "F:\Access\Samples\New MDBs\db1.mdb"
  11.         Set tdfNewTable = .CurrentDb.CreateTableDef("tbl")
  12.         With tdfNewTable
  13.             Debug.Print .Updatable
  14.             .Fields.Append .CreateField(Name:="keyID", Type:=dbLong)
  15.             .Fields.Append .CreateField(Name:="txtField", Type:=dbText)
  16.         End With
  17.         .CurrentDb.TableDefs.Append tdfNewTable
  18.         .CloseCurrentDatabase
  19.         .Quit acQuitSaveAll
  20.     End With
  21.     Set appAccess = Nothing
  22.  
  23.     With CurrentDb
  24.         Set tdfNewTable = .CreateTableDef("tblLinked", dbAttachExclusive, "tbl", _
  25.                           ";DATABASE=F:\Access\Samples\New MDBs\db1.mdb")
  26.         .TableDefs.Append tdfNewTable
  27.     End With
  28.  
  29.     Set tdfNewTable = Nothing
  30.  
  31. End Sub
  32.  
BTW Access object library is referenced by default.

Good luck.
Jul 24 '07 #4

Expert 5K+
P: 8,434
Here is very basic code creating new mdb, adding to it a simple table and linking it to the current db. ...
Thanks! All contributions welcome. :)

BTW Access object library is referenced by default.
In VB6? Are you sure of that? The reason I thought about using it is that I noticed it, unchecked, on the list of available libraries.

Hang on, I'll go check...

Nope. I'll have to conclude that you're referring to VBA.
Jul 24 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Thanks! All contributions welcome. :)

In VB6? Are you sure of that? The reason I thought about using it is that I noticed it, unchecked, on the list of available libraries.

Hang on, I'll go check...

Nope. I'll have to conclude that you're referring to VBA.
Sure this is VBA. Sorry, didn't pay attention you want it in VB6.
Jul 24 '07 #6

Expert 5K+
P: 8,434
... Here is very basic code creating new mdb, adding to it a simple table and linking it to the current db...
Thanks for that. I've been playing with it have managed to create my Mdb, add the table and add my fields and indexes. What I can't work out so far is how to turn on Unicode compression for a field. Any ideas?

My databases are going to be pretty big, and I don't want to make them even bigger by leaving this option turned off. But since I can't find anything in the DAO documentation, I'm guessing it's Access-specific and DAO doesn't know about it? Or perhaps I need to put a particular value in .Attributes for the field or something?
Jul 26 '07 #7

FishVal
Expert 2.5K+
P: 2,653
Thanks for that. I've been playing with it have managed to create my Mdb, add the table and add my fields and indexes. What I can't work out so far is how to turn on Unicode compression for a field. Any ideas?

My databases are going to be pretty big, and I don't want to make them even bigger by leaving this option turned off. But since I can't find anything in the DAO documentation, I'm guessing it's Access-specific and DAO doesn't know about it? Or perhaps I need to put a particular value in .Attributes for the field or something?
It is in Field.Properties collection. As well as almost all other field properties available in table design view.
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tbl").Fields("txtText").Properties("UnicodeCompression").Value = True
  2.  
Jul 26 '07 #8

Expert 5K+
P: 8,434
It is in Field.Properties collection. As well as almost all other field properties available in table design view.
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tbl").Fields("txtText").Properties("UnicodeCompression").Value = True
Guess I didn't quite get it right. Most likely I'm referencing the wrong "field" type object or something. I've got ADO and DAO floating around, plus the Access object library - it's getting confusing. Anyway, I get "Property not found".

I didn't follow your suggestion exactly - I've already got the tabledef and fielddef there, so didn't feel it necessary to use the longer references. Anyway, details below.

Hope you can help a little further on this. Based on your example, I originally had this line, which works...
Expand|Select|Wrap|Line Numbers
  1. .Fields.Append .CreateField("MyField", dbText, 20)
In order to try and set the Unicode compression, I created a new temp object and used it as follows...
Expand|Select|Wrap|Line Numbers
  1. Dim TempField As DAO.Field
  2.  
  3. Set TempField = .CreateField("MyField", dbText, 20)
  4. ' The following line produces error "Property Not Found"...
  5. TempField.Properties("UnicodeCompression").Value = True
  6. .Fields.Append TempField
Jul 27 '07 #9

Expert 5K+
P: 8,434
Oh, one more question. Everything apart from the Unicode compression seems to be working (and I can probably live with that). But how can I stop Access from flashing up on the screen each time I call my create-a-table routine?

This is not critical. It's only visible very briefly, and this will only happen once every few months. But it's really annoying - especially during testing, which has to create a whole bunch.
Jul 27 '07 #10

P: 17
In VBA it's DoCmd.SetWarnings False.
Jul 27 '07 #11

FishVal
Expert 2.5K+
P: 2,653
Guess I didn't quite get it right. Most likely I'm referencing the wrong "field" type object or something. I've got ADO and DAO floating around, plus the Access object library - it's getting confusing. Anyway, I get "Property not found".

I didn't follow your suggestion exactly - I've already got the tabledef and fielddef there, so didn't feel it necessary to use the longer references. Anyway, details below.

Hope you can help a little further on this. Based on your example, I originally had this line, which works...
Expand|Select|Wrap|Line Numbers
  1. .Fields.Append .CreateField("MyField", dbText, 20)
In order to try and set the Unicode compression, I created a new temp object and used it as follows...
Expand|Select|Wrap|Line Numbers
  1. Dim TempField As DAO.Field
  2.  
  3. Set TempField = .CreateField("MyField", dbText, 20)
  4. ' The following line produces error "Property Not Found"...
  5. TempField.Properties("UnicodeCompression").Value = True
  6. .Fields.Append TempField
Hi.

I've checked it and here are results.
  • Not all properties are automatically created when Field object is created (20 properties for Field (text type) created programmatically vs 27 for that one created in table design view), "UnicodeCompression" among them
  • CreateProperty method may be used to create property
  • Properties.Append method works only on Field which is "in a Microsoft Jet workspace" only (Access help says), so TableDef object has to be appended to TableDefs collection before Property will be appended to Field.Properties collection

So the code is the following:
Expand|Select|Wrap|Line Numbers
  1.     ....................
  2.     Dim proNewProperty As DAO.Property
  3.     ................
  4.     [Database object].TableDefs.Append tdfNewTable
  5.     Set proNewProperty = [Database object].CreateProperty(Name:="UnicodeCompression", _
  6.         Value:=True, Type:=dbBoolean)
  7.     [Database object].TableDefs("tbl").Fields("txtField").Properties.Append proNewProperty
  8.     ..................
  9.  
Jul 27 '07 #12

FishVal
Expert 2.5K+
P: 2,653
Oh, one more question. Everything apart from the Unicode compression seems to be working (and I can probably live with that). But how can I stop Access from flashing up on the screen each time I call my create-a-table routine?

This is not critical. It's only visible very briefly, and this will only happen once every few months. But it's really annoying - especially during testing, which has to create a whole bunch.
So far I have completely no idea how to fix it. Plz let me know if you'll find a solution. ;)
Jul 27 '07 #13

Expert 5K+
P: 8,434
So far I have completely no idea how to fix it. Plz let me know if you'll find a solution. ;)
It's likely to be a while before I have a chance to look for one, but I'll keep you in mind if/when I do.

As for the property stuff, I'll give it a try. Thanks for all your help. (This reminds me of why I came to TheScripts in the first place.)
Jul 28 '07 #14

Expert 5K+
P: 8,434
In VBA it's DoCmd.SetWarnings False.
Thanks for that. Unfortunately I'm working in VB6, so I don't know how (or whether) I can make use of this. Will let everyone know if I find out.
Jul 29 '07 #15

Expert 5K+
P: 8,434
I have a few things to report.
  1. I managed to get the Unicode compress property added and working. Thanks for the info.
    It took a while, as I kept running into silly little non-intuitive things that you apparently just have to know. Like the fact that if I create a property, then append it to a fields Properties collection, I have to create it again before I can append it to another.
  2. In case anyone is interested, it's upon executing the .Quit acQuitSaveAll that the Access window flashes briefly but annoyingly on the screen.
  3. For some reason, when I add the "unicodecompression" property, I no longer get the Access window flashing briefly on the screen. I've tried adding and removing the code, and this seems to be what makes the difference.
  4. Also for unknown reasons, I cannot use a For Each loop to go through the fields in the tabledef to add the new property.
    Let's say I start out with this...
    Expand|Select|Wrap|Line Numbers
    1. Set prpNewProperty = .CurrentDb.CreateProperty("UnicodeCompression", dbBoolean, True)
    This works...
    Expand|Select|Wrap|Line Numbers
    1.     .CurrentDb.TableDefs(TempTableName).Fields("Field1").Properties.Append prpNewProperty
    But if I try this...
    Expand|Select|Wrap|Line Numbers
    1. For Each fldNewField In .CurrentDb.TableDefs(TempTableName).Fields
    2.   With fldNewField
    3.     If .Type = dbText Then
    4.       .Properties.Append prpNewProperty
    5.     End If
    6.   End With
    7. Next
    8.  
    it fails on the For Each, with Object invalid or no longer set. I believe this is not due to trying to use prpNewProperty more than once, as it bombs the instant it hits the For Each.
Jul 31 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.