Adding fields to an existing Access 2002 table

[sphinney]

I'm really embarrased to ask this one, but I can't seem to figure out how to add a new field into and existing table (at least how to do it correctly!)

The simple code below gives me an error: "Object invalid or no longer set"

Dim ELLIS_TD As DAO.TableDef
Set ELLIS_TD = CurrendDB.Tabledefs("VALID_PID")
ELLIS_TD.Fields.Append ELLIS_TD.CreateField("DESCRIPTION", dbString, 50)

I've checked a thousand times and "VALID_PID" is a real tabledef in the current database. Even the watch that I created says all of the properties of ELLIS_TD are "Object Invalid or no longer set" after the "Set" statement executes.

What am I doing wrong? I seems like the "Set" statement is wrong, buy why?

Thanks for the help!
Scott

[Scott Price]

Just noticed one little thing: You spelled it CurrendDb (should be CurrentDb)...

I'm thinking also that it would help to separate these commands onto their own lines of code. This makes it much easier to read, then comment, then debug.

Regards,
Scott

[missinglinq]

Two questions. Why are you trying to do this thru code instead of simply going into Table Design View? Do you have the DAO Reference set?

Linq :)>

[sphinney]

Scott and Linq's,

Thanks to both of you for your posts. If it's OK, I'd like to respond to both of you with a single post.

Scott - You're right, I did mispell CurrentDB in my post. It's spelled correctly in my module. I tried to retype the code instead of copy/paste. Oops!

Linq - The reason I'm adding the field by code is a long one, but I'll try to be concise:
- I'm executing a series of 22 SQL statements against my company's SYBASE server.
- Because of the complexity of the WHERE clause in each statement, it's MUCH easier to execute them separately, than all together.
- Once the FIRST statement is executed, I use the returned recordset to create a new tabledef in Access and add the data to it.
- Subsequent SQL statements retrieve additional data that I want to join into the first table.
- The number records returned by subsequent SQL statements is different for each statement, therefore, I also use a FindFirst method on the first recordset to tied key fields together and match up the data.
- I did try to making separate tables for all 22 SQL statements and designing an Access query to join them all. But Access REALLY doesn't like trying to join 22 different tables. I've got a pretty decent desktop computer, but it hangs big time when I do this.
- So, since joining 22 tables with a query didn't work (and the SQL statements may/will change in the future) I wanted to use code to add fields and data instead of having to use Table Design View

Finally, I do have a reference set for DAO.

Any other thoughts? I've looked at my "Set" statement for a day and a half, now. It should work.

Thanks, again, for your help.
Scott

[FishVal]

Hi, sphinney.

Try to avoid using object variable referencing TableDef. As far as I recall I've had the similar troubles. From somewhat reasons Access doesn't allow to hold references to some objects in variables.

CurrendDB.Tabledefs("VALID_PID").Fields.Append ELLIS_TD.CreateField("DESCRIPTION", dbString, 50)

[FishVal]

Hi, sphinney.

Try to avoid using object variable referencing TableDef. As far as I recall I've had the similar troubles. From somewhat reasons Access doesn't allow to hold references to some objects in variables.

CurrendDB.Tabledefs("VALID_PID").Fields.Append ELLIS_TD.CreateField("DESCRIPTION", dbString, 50)

Or even better. Use SQL to modify table structure

ALTER TABLE VALID_PID ADD COLUMN DESCRIPTION Text(50);
 

Regards,
Fish.

[FishVal]

Sphinney,

I've read your explanations and still not sure you should do it via additional fields. Provide your tables structure and queries you use to let somebody from the community to come up with certain advices.


Kind regards,
Fish.

[sphinney]

Sphinney,

I've read your explanations and still not sure you should do it via additional fields. Provide your tables structure and queries you use to let somebody from the community to come up with certain advices.


Kind regards,
Fish.

Thanks for your help, Fish. I've tried not to put too much information in my posts because I'm trying not to overwhelm people with irrelavent information.

In short, I have 22 different queries that I'm running against various tables on a SYBASE server. The SYBASE server is connected via OBDC. I need to join the results of all 22 queries. Because the queries are so complex (see sample below), I can't run them together as a single query.

I use the following code to run each query. I've altered the connection string for security purposes, but it runs just fine.

Public Sub QUERY_ELLIS(SQLstr As String)
 
Dim CON
Dim RS
Dim SQLstr as string
 
Set CON = CreateObject("ADODB.Connection")
CON.ConnectionString = "DSN=********;uid=********;pwd=********"
 
CON.Open
Set RS = CON.Execute(SQLstr)
 
End Sub

With the code above, "RS" is a recordset returned by the SQL.

At this point I'm open to any suggestions how to join the recordsets from all 22 queries. Only one of the queries (the first one I run) returns the exact number of records I need. The other 21 queries return more or less records than the first query. There is the same key field ("PID_NBR") in all of the queries so I can use that field to match up the data. I really want to join all of the data into one table so I can delete the records I don't need.

My initial thought was to run the first query, create a tabledef in Access that matches the query recordset structure, append the tabledef to the current Access database, then add the data. For each subsequent query, I would add fields to the tabledef as necessary, match up the key fields, then add the data. This is where I ran into trouble. The "Set" line in the following code doesn't seem to really reference the tabledef when I'm trying to add fields:

Dim JOIN_TO as String
Dim ELLIS_TD As DAO.TableDef
Set ELLIS_TD = CurrentDb.TableDefs(JOIN_TO)

I hope all this make sense. Below is a sample of the queries that I'm running. This query works fine, I'm only including it as a example. This query is for SYBASE so it looks a bit different than Microsoft Jet SQL.

Thanks for the help,
Scott

****SAMPLE SQL*****

select 
    ELLIS_PROJECT3.PID_NBR, 
    SUM (ELLIS_FUNDING_DETAIL2.ADJUSTED_TOTAL_AMT) 
from 
    ELLIS_FUNDING_DETAIL ELLIS_FUNDING_DETAIL2, 
    PAS_IQ_FUNDS PAS_IQ_FUNDS1b, 
    ELLIS_ENCUMB_REQUEST ELLIS_ENCUMB_REQUEST2, 
    ELLIS_PROJECT ELLIS_PROJECT3 
where 
    ((ELLIS_ENCUMB_REQUEST2.FISCAL_YR = «SFY») 
    and 
    (ELLIS_FUNDING_DETAIL2.ENCUMBERED_IND = 'F') 
    and 
    (ELLIS_FUNDING_DETAIL2.ADJUSTED_TOTAL_AMT > 0.00) 
    and 
    (ELLIS_PROJECT3.PROJECT_STATUS_CD != 4) 
    and 
    (ELLIS_FUNDING_DETAIL2.COMMITTED_IND = 'Y') 
    and 
    (ELLIS_PROJECT3.LETTING_SHORT_NME IN('Local Let', 'ODOT Let')) 
    and 
    (ELLIS_FUNDING_DETAIL2.SUBPHASE_CD = 12) 
    and 
    (ELLIS_PROJECT3.PID_NBR NOT IN (select ELLIS_TRACKER_MILESTONES.PID_NBR from ELLIS_TRACKER_MILESTONES where (            ELLIS_TRACKER_MILESTONES.ACTUAL_DT_23 IS NOT NULL))) 
    and 
    (PAS_IQ_FUNDS1b.CATEGORY_NME NOT IN ('All non Maj            or New Programs', 'District Maintenance Contracts', 'District Preservation (Pavemnt & Bridge)','Garvee Bonds', 'Local Let', 'Major Bridge', 'Major New', 'Multi-Lane Major Rehab', 'Railroad Grade Separation',     'Safety',         'Traditional Programs', 'Demo', 'Discretionary', 'High Priority',                 'Local Programs')) 
    and 
    (PAS_IQ_FUNDS1b.FUND_PARENT_CD1 != 'TRAC')) 
    and 
    ((PAS_IQ_FUNDS1b.BILL_FUND_IND = 'N'))  
    and 
    ELLIS_FUNDING_DETAIL2.ESTIMATE_FUND_OID = PAS_IQ_FUNDS1b.FUND_OID 
    and 
    ELLIS_ENCUMB_REQUEST2.FUNDING_EVENT_OID = ELLIS_FUNDING_DETAIL2.FUNDING_EVENT_OID 
    and 
    ELLIS_FUNDING_DETAIL2.PID_NBR = ELLIS_PROJECT3.PID_NBR 
group by 
    ELLIS_PROJECT3.PID_NBR 
order by 1

[FishVal]

Hi, Sphinney.

You almost scared me with your query.
But still no sense in adding fields.

You try to put all information imported into one table. Sure it is supposed to grow in two directions. But this is unnatural for relational databases.

Why not to go with normalized table structure?

Your current table is classicaly denormalized.

 tblYourTable
PID_NBR      PK
Field1
Field2
......
Field22
 

I would like the following:

 tblFieldNames
keyFieldNameID              PK, Long(Autonumber)
txtFieldName                Text
 
 tblImport
keyID                       PK, Long(Autonumber)
keyFieldNameID              FK(tblFieldNames.keyFieldNameID)
PID_NBR                    
DataField                   Field1-22 data type