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

Adding fields to an existing Access 2002 table

P: 69
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"

Expand|Select|Wrap|Line Numbers
  1. Dim ELLIS_TD As DAO.TableDef
  2. Set ELLIS_TD = CurrendDB.Tabledefs("VALID_PID")
  3. 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
Oct 4 '07 #1
Share this Question
Share on Google+
8 Replies


Scott Price
Expert 100+
P: 1,384
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
Oct 4 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
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 :)>
Oct 4 '07 #3

P: 69
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
Oct 4 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. CurrendDB.Tabledefs("VALID_PID").Fields.Append ELLIS_TD.CreateField("DESCRIPTION", dbString, 50)
Oct 4 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. CurrendDB.Tabledefs("VALID_PID").Fields.Append ELLIS_TD.CreateField("DESCRIPTION", dbString, 50)
Or even better. Use SQL to modify table structure
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE VALID_PID ADD COLUMN DESCRIPTION Text(50);
  2.  
Regards,
Fish.
Oct 4 '07 #6

FishVal
Expert 2.5K+
P: 2,653
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.
Oct 4 '07 #7

P: 69
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.

Expand|Select|Wrap|Line Numbers
  1. Public Sub QUERY_ELLIS(SQLstr As String)
  2.  
  3. Dim CON
  4. Dim RS
  5. Dim SQLstr as string
  6.  
  7. Set CON = CreateObject("ADODB.Connection")
  8. CON.ConnectionString = "DSN=********;uid=********;pwd=********"
  9.  
  10. CON.Open
  11. Set RS = CON.Execute(SQLstr)
  12.  
  13. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim JOIN_TO as String
  2. Dim ELLIS_TD As DAO.TableDef
  3. 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*****

Expand|Select|Wrap|Line Numbers
  1. select 
  2.     ELLIS_PROJECT3.PID_NBR, 
  3.     SUM (ELLIS_FUNDING_DETAIL2.ADJUSTED_TOTAL_AMT) 
  4. from 
  5.     ELLIS_FUNDING_DETAIL ELLIS_FUNDING_DETAIL2, 
  6.     PAS_IQ_FUNDS PAS_IQ_FUNDS1b, 
  7.     ELLIS_ENCUMB_REQUEST ELLIS_ENCUMB_REQUEST2, 
  8.     ELLIS_PROJECT ELLIS_PROJECT3 
  9. where 
  10.     ((ELLIS_ENCUMB_REQUEST2.FISCAL_YR = «SFY») 
  11.     and 
  12.     (ELLIS_FUNDING_DETAIL2.ENCUMBERED_IND = 'F') 
  13.     and 
  14.     (ELLIS_FUNDING_DETAIL2.ADJUSTED_TOTAL_AMT > 0.00) 
  15.     and 
  16.     (ELLIS_PROJECT3.PROJECT_STATUS_CD != 4) 
  17.     and 
  18.     (ELLIS_FUNDING_DETAIL2.COMMITTED_IND = 'Y') 
  19.     and 
  20.     (ELLIS_PROJECT3.LETTING_SHORT_NME IN('Local Let', 'ODOT Let')) 
  21.     and 
  22.     (ELLIS_FUNDING_DETAIL2.SUBPHASE_CD = 12) 
  23.     and 
  24.     (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))) 
  25.     and 
  26.     (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')) 
  27.     and 
  28.     (PAS_IQ_FUNDS1b.FUND_PARENT_CD1 != 'TRAC')) 
  29.     and 
  30.     ((PAS_IQ_FUNDS1b.BILL_FUND_IND = 'N'))  
  31.     and 
  32.     ELLIS_FUNDING_DETAIL2.ESTIMATE_FUND_OID = PAS_IQ_FUNDS1b.FUND_OID 
  33.     and 
  34.     ELLIS_ENCUMB_REQUEST2.FUNDING_EVENT_OID = ELLIS_FUNDING_DETAIL2.FUNDING_EVENT_OID 
  35.     and 
  36.     ELLIS_FUNDING_DETAIL2.PID_NBR = ELLIS_PROJECT3.PID_NBR 
  37. group by 
  38.     ELLIS_PROJECT3.PID_NBR 
  39. order by 1
Oct 4 '07 #8

FishVal
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. tblYourTable
  2. PID_NBR      PK
  3. Field1
  4. Field2
  5. ......
  6. Field22
  7.  
I would like the following:
Expand|Select|Wrap|Line Numbers
  1. tblFieldNames
  2. keyFieldNameID              PK, Long(Autonumber)
  3. txtFieldName                Text
  4.  
  5. tblImport
  6. keyID                       PK, Long(Autonumber)
  7. keyFieldNameID              FK(tblFieldNames.keyFieldNameID)
  8. PID_NBR                    
  9. DataField                   Field1-22 data type
  10.  
Oct 6 '07 #9

Post your reply

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