Solved

Is field there? If not add it.

Posted on 2002-05-25
6
173 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 10 hours left to enroll

627 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