Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Determine the table field differences

Posted on 2012-03-20
9
Medium Priority
?
343 Views
Last Modified: 2012-04-11
Looking for code to determine the differences between two tables' number of fields and which table does not have the certain fields.

I need to compare two tables between different databases and determine in variances in the number of fields and which tables/fields are different.

Looking for code to determine the differences?

K
0
Comment
Question by:Karen Schaefer
[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
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743842
You are not presenting a clear example of two different tables and the *exact* output you are expecting, ...
So creating a targeted solution is difficult...

See here:
http://www.fmsinc.com/microsoftaccess/databasecompare.html
...Why re-invent the wheel?

JeffCoachman
0
 

Author Comment

by:Karen Schaefer
ID: 37743873
Jeff

can't get FMS - company does not have license - can't buy one either.  Looking for freeware or shareware.

I have several databases versions of the same database where some of the older version's table structure has not been updated to include newer fields.  Trying to determine which version and tables contain these discrepancies and some how programically allow these differences in my latest version of the database.

Table1.PartnerID & table2 does not contain field for PartnerID.

If table 2 is different I need to change the record source for the form to create a temp field in my query for the Partner field so I do not get a missing object error.

any ideas?

K
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743924
Again, please provide a more comprehensive example, and post the *exact* output you are seeking...
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Karen Schaefer
ID: 37743966
I want to see which table contains the field PartnerID and which of the two tables don't - and if there is a difference the create a query to look like.

Table 1 - Select RecID, EmployeeName, PartnerID from table1
Table 2 = Select RecID, EmployeeName, "" as PartnerID from table2  

changing the record source for the data Entry form to "Select RecID, EmployeeName, "" as PartnerID from table2 "  etc.

I just want to identify which tables do not contain the same # of fields

So compare field count of Table 1 to Field Count of Table 2, if different, then determine which fields are missing from Table 2 and create a recordsource that allows for the missing fields.

Hope this helps you understand.

Since each of the older versions' table structure may have several missing fields.

K
0
 

Author Comment

by:Karen Schaefer
ID: 37743978
I can not change the actual older versions table structure I just want to bring in the older tables where applicable and handle any discrepancies that could cause the latest version of the database to display error messages for missing fields.

K
0
 

Author Comment

by:Karen Schaefer
ID: 37744002
As you probably know from previous post my database designs are not the most relationship oriented.  I have 100's copies of the the databases where the versions vary.  I can't change to a proper FE & BE situation.  I am stuck with what I have I just want to be able to push out the latest updates to ever version so I do not have multiple versions of the databases out there.  But in order to accomplish I need to be able to handle the variances in the table structures.  I have looked into version control, but still run into the differences in the actual structure issues.  The older databases are well into their life cycle and I cannot disrupted, trying to accomplish this task is humongous.

For the Partner detail info - some of the older versions will not have this type of data in their databases and this is just one example of the issues facing me.

K
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37744077
As always, ...Anything is possible, ...but your request, (while straightforward), would be fairly time consuming to create from scratch. (For me at least)

Lets see if another Expert is willing to take this (or part of this) on...


JeffCoachman
0
 

Author Closing Comment

by:Karen Schaefer
ID: 37833791
Award points for time and effort
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833926
No need...
Again, accepting a post for "Effort" will mark it as the "Solution"...
This will be confusing for others searching here for the same issue...

Just request that the Q be deleted...


Jeff
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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