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
brayleAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
EDDYKTCommented:
Can you do select command and check fro return status
0
 
brayleAuthor Commented:
I don't know how.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
brayleAuthor Commented:
Fair enough. I think I was confused because I was getting comments rather than answers. Taken care of now.
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
All Courses

From novice to tech pro — start learning today.