Create Table

[Hydra]

OK, I've got code that creates a database and creates a TableDef, but I
cannot figure out how to turn the Tabledef into a table that I can populate.
I've got code that shows how to populate an existing table from and Excel
file, but Ineed to fisrt creat the table.

Heve not found anything in help, or object browser, or external text, yet
this would seemto be a pretty basic operation.

What's worse it that I know I have done it before........

 

[fredg]

OK, I've got code that creates a database and creates a TableDef, but I
cannot figure out how to turn the Tabledef into a table that I can populate.
I've got code that shows how to populate an existing table from and Excel
file, but Ineed to fisrt creat the table.

Heve not found anything in help, or object browser, or external text, yet
this would seemto be a pretty basic operation.

What's worse it that I know I have done it before........

I assume there is a reason to do this programmatically, rather than
just clicking New from the Database Table Objects window.

Any reason why you haven't used the Help files already on your
computer?

Help + Contents + Microsoft Access Help + Microsoft Data Access
Objects 3.60 + Microsoft DAO 3.60 + DAO Methods Reference + C +
CreateTableDef Method.

Or......

from the Help Contents menu navigate to:

Microsoft Jet SQL Reference + Data Definition Language + Create Table
Statement

Both methods include examples.

 

[Hydra]

I'm using the help screens and all they show is create tabledef.
Is there anyway they could make the help screens more obtuse, or create less
useful examples, or "see alsos" that point nowhere?


The reason I'm doing this programmatically is that it creates a new database
and a new table based on named ranges in an excel file. I won't know in
advance exactly which ranges exist, because the excel table is created from a
pick list in a third application.

I'm sure it is in there somewhere, and I'm a blithering idiot for not being
able to find it, but so far, no luck.

So the drilll is to have Excel Open and creat a database, create a table (or
tables) in that database, and then populate the table(s) fields with data
from the named ranges.

I know that you can create a tabledef so you can manipulate the properties
you want in your table, and then use the tabledef to create the table.

From the help screens:

"You manipulate a table definition using a TableDef object and its methods
and properties. For example, you can: " ......

Which is fine to know you can do that, but nowhere that I've found does it
say "HOW". It says you can creat a table from the tabledef by using the
OpenRecordSet method, but when you look that up it mrerely says

"Creates a new Recordset object and appends it to the Recordsets collection."

I understand that recordests are usually query driven subsets of a table or
tables. There is also a table type recordset object, and when you look that
up it tells you you can create it from a table!

That is not very helpful of the help screens because I'm trying to create
the table I need the recordset for. Neither are my textbooks helpful on this
subject.

 

[Hydra]

Another example"

"You can use DAO objects, such as the Database, TableDef, Recordset, and
QueryDef objects, to represent objects that are used to organize and
manipulate data, such as tables and queries, in code."

OK, Swell, glad to know that.

So I have a TableDef that represents a table, which organizes my data. How
do I convert the table def into an actual table? (OK actual is meaningless
since both the Tabledef and the table are just digital constructs.) Why would
I want to use an object to represent another object? Why not use the table
object and manipulate data there?

 

[Douglas J. Steele]

You say you've got code that "creates a database and creates a TableDef".
What does the code look like? Creating a TableDef object is supposed to
create a table, so what you're describing doesn't really make sense.

 

[Hydra]

"Creating a TableDef object is supposed to create a table, so what you're
describing doesn't really make sense."

Now I don't feel so bad, it doewn't make sense to me, either.

Code looks like


If Dir("C:\Cobra conversion\DataCheck.mdb") <> "" Then
'MsgBox "Datacheck Exists"
Kill "C:\Cobra Conversion\DataCheck.mdb"
End If
Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
With appAccess
..NewCurrentDatabase ("C:\Cobra Conversion\DataCheck.mdb")
..Visible = True
End With
Set dbs = appAccess.CurrentDb


' ----------Create a new TableDef object.
Set tdfResources = dbs.CreateTableDef("Resources")


'------------Populate the TableDef with names for each range in the
Spreadsheet
'------------Pick up the names Fromt he Workbook Names collection
For r = 1 To ActiveWorkbook.Names.Count
'MsgBox (ActiveWorkbook.Names(r).Name)
Rangename = ActiveWorkbook.Names(r).Name

'------------Then , using the TableDef Created above:
With tdfResources


'--------- Create fields and append them to the new TableDef
'--------- object. This must be done before appending the
'--------- TableDef object to the TableDefs collection of the database
.Fields.Append .CreateField(""" & Rangename & """, dbText)

'--------Finish with this loop through the TableDef
End With
'--------Pick up the next Rangename to add to the tabledef
Next r

'-----------End of looping through rangename and TableDef
'---------- Append the new TableDef object to the database.
dbs.TableDefs.Append tdfResources


----------------------------------------------------------------------------------

All of this apparently works, at least it doesn't throw any errors, but the
database does not show any tables appended, yet.



On the other hand, this did work In which case I don't need the tabledef:

Set dbs = OpenDatabase("C:/Cobra Conversion/datacheck")
dbs.Execute "CREATE TABLE Resources (ResourceID, Period, WBS, Hours,
Units )"
dbs.OpenTable ("Resources")
appAccess.RefreshDatabaseWindow


But now seems to be broken (sigh) This won't meet my needs since I need to
loop through and pick up the rangenames as above.

 

[Hydra]

I finally bumbled through something that works, but little thanks to the help
screens.