Solved

Is field there? If not add it.

Posted on 2002-05-25
6
172 Views
Last Modified: 2012-05-04
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
Comment
Question by:brayle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:EDDYKT
ID: 7034639
Can you do select command and check fro return status
0
 

Author Comment

by:brayle
ID: 7034649
I don't know how.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7034669
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:brayle
ID: 7034676
Fair enough. I think I was confused because I was getting comments rather than answers. Taken care of now.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 7034797
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
 

Author Comment

by:brayle
ID: 7034825
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question