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

[Killer42]

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.

[Lysander]

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

[Killer42]

... 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.

[FishVal]

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.

Public Sub CreateNewMDB()
 
    Dim appAccess As Access.Application
    Dim tdfNewTable As TableDef
 
    Set appAccess = CreateObject("Access.Application")
 
    With appAccess
        '.Visible = True
        .NewCurrentDatabase "F:\Access\Samples\New MDBs\db1.mdb"
        Set tdfNewTable = .CurrentDb.CreateTableDef("tbl")
        With tdfNewTable
            Debug.Print .Updatable
            .Fields.Append .CreateField(Name:="keyID", Type:=dbLong)
            .Fields.Append .CreateField(Name:="txtField", Type:=dbText)
        End With
        .CurrentDb.TableDefs.Append tdfNewTable
        .CloseCurrentDatabase
        .Quit acQuitSaveAll
    End With
    Set appAccess = Nothing
 
    With CurrentDb
        Set tdfNewTable = .CreateTableDef("tblLinked", dbAttachExclusive, "tbl", _
                          ";DATABASE=F:\Access\Samples\New MDBs\db1.mdb")
        .TableDefs.Append tdfNewTable
    End With
 
    Set tdfNewTable = Nothing
 
End Sub
 

BTW Access object library is referenced by default.

Good luck.

[Killer42]

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.

[FishVal]

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.

[Killer42]

... 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?

[FishVal]

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.

CurrentDb.TableDefs("tbl").Fields("txtText").Properties("UnicodeCompression").Value = True
 

[Killer42]

It is in Field.Properties collection. As well as almost all other field properties available in table design view.

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...

.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...

Dim TempField As DAO.Field
 
Set TempField = .CreateField("MyField", dbText, 20)
' The following line produces error "Property Not Found"...
TempField.Properties("UnicodeCompression").Value = True
.Fields.Append TempField

[Killer42]

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.

[rewalk]

In VBA it's DoCmd.SetWarnings False.

[FishVal]

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...

.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...

Dim TempField As DAO.Field
 
Set TempField = .CreateField("MyField", dbText, 20)
' The following line produces error "Property Not Found"...
TempField.Properties("UnicodeCompression").Value = True
.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:

    ....................
    Dim proNewProperty As DAO.Property
    ................
    [Database object].TableDefs.Append tdfNewTable
    Set proNewProperty = [Database object].CreateProperty(Name:="UnicodeCompression", _
        Value:=True, Type:=dbBoolean)
    [Database object].TableDefs("tbl").Fields("txtField").Properties.Append proNewProperty
    ..................
 

[FishVal]

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. ;)

[Killer42]

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.)

[Killer42]

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.

[Killer42]

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...

   Set prpNewProperty = .CurrentDb.CreateProperty("UnicodeCompression", dbBoolean, True)

   This works...

       .CurrentDb.TableDefs(TempTableName).Fields("Field1").Properties.Append prpNewProperty

   But if I try this...

   For Each fldNewField In .CurrentDb.TableDefs(TempTableName).Fields
     With fldNewField
       If .Type = dbText Then
         .Properties.Append prpNewProperty
       End If
     End With
   Next
    

   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.