• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Is field there? If not add it.

My app has been distributed with an Access database via DAO.

Now I have added a new field "UsersVersion" to the table "tblUserSettings."

All is fine for new users, but when I send an update out to current users they keep their old database. But the updated app looks for the new field info, its not there in the older databases, They get "Item not in collection" error.

So I need to have my app check to see if the new field is in the current database, and if not, add it.

Examples using my table and field name would be most helpful, I am green on database stuff.

thanks
0
brayle
Asked:
brayle
  • 3
  • 2
1 Solution
 
EDDYKTCommented:
Can you do select command and check fro return status
0
 
brayleAuthor Commented:
I don't know how.
0
 
Anthony PerkinsCommented:
Please maintain your open questions:

How to get system name Date: 12/05/2001 05:08AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20243866
BitBlt to printer Date: 11/24/2001 02:40AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20239497
Can I use floodfill on a printer? Date: 11/20/2001 01:19PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20237469

Thanks,
Anthony
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
brayleAuthor Commented:
Fair enough. I think I was confused because I was getting comments rather than answers. Taken care of now.
0
 
Anthony PerkinsCommented:
Typically in EE, solutions are always posted as comments.

The following code, does what you are looking for:

Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fld As DAO.Field

Set db = OpenDatabase("c:\temp\db1.mdb")
Set tb = db.TableDefs("tblUserSettings")
If tb.Fields.Count <= 3 Then 'Change as appropriate
   Set fld = tb.CreateField("UsersVersion", dbText, 50)
   tb.Fields.Append fld
End If
db.Close

Notes:
1. As you notice, it assumes you know ahead of time the number of fields, in order to know whether to add the field or not.  Another method is to loop through until you find or not the column.
2. I assumed text, lenght 50 as you did not mention otherwise
3. Make sure there are no recordsets open on this table when you try to add the column.  One way is to test immediately you open the database.
4. I have not used DAO in 4 years, but it should work, without any problem.

Anthony
0
 
brayleAuthor Commented:
Thanks, it doesn't get any better. Tried it it worked.
Especially appreciate you using my details and the notes!

If there was a higher grade I would give it, have boosted points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now