Determine the table field differences

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?

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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:
...Why re-invent the wheel?

Karen SchaeferBI ANALYSTAuthor Commented:

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?

Jeffrey CoachmanMIS LiasonCommented:
Again, please provide a more comprehensive example, and post the *exact* output you are seeking...
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Karen SchaeferBI ANALYSTAuthor Commented:
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.

Karen SchaeferBI ANALYSTAuthor Commented:
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.

Karen SchaeferBI ANALYSTAuthor Commented:
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.

Jeffrey CoachmanMIS LiasonCommented:
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...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Award points for time and effort
Jeffrey CoachmanMIS LiasonCommented:
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...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.