Linking field names

[BroncoMarc]

I have an existing database that has self contained tables. I found a table on an SQL server in the company that has better info and can replace one of my existing tables. The problem is that a couple of the field names are different.

This is a pretty big database (lot of forms and code, etc).. I don't want to search thru the whole database and change all the field names to match the field names on the SQL table. I can't change the names of the fields on the SQL table.

Is there an easy way to tell it that, for example AccountID (the field name on the SQL table) = Parent_Customer_Number (What the database forms, etc are looking for) ??

Thanks,
- Marc

 

[Rob.Mills]

What you could do is setup a query and pull all the fields in.

For each field where the name is different from the server table do this:

Say the Server field name is Record_ID and your local table has the same field named RecordID then in your query put:

RecordID: Record_ID

Do that for each field.

Then base everything off that query.

 

[BroncoMarc]

Wouldn't that still involve going thru the whole application and changing everything to look at the query rather than the table?

The only thing I can think of is to make a query to create a local table from the SQL table, then base the application off the new table. But then I'd have to run that query every time I wanted to update the data from the SQL table.

There's gotta be a better way.

- Marc

 

[Rob.Mills]

No you wouldn't have to do that. If you base anything off that query that I told you then the only thing you would see as the field name is the part before the colon.

Try creating the query I explained. Then create another query and add the first query to it. All you will see is the name you're looking for.

 

[Pat Hartman]

The final piece of the trick is to name the query with the same name as your old Access table.

 

[Rob.Mills]

That's right! Thanks Pat. A small but important detail I forgot.

 

[BroncoMarc]

Your saying if I create a query with the same name as the table everything in the application will look at the query rather than the table??

Everything in the application points to a table called CustomerData. If I did what you say, wouldn't I still have to go thru the whole application and change all the references to look for a query called CustomerData?

- Marc

 

[Rob.Mills]

Actually you'd probably need to delete that table or rename it. If you need to use that table a lot then there's no way of getting around making some changes.

But if you were to delete the table then all the forms and reports would automatically pick up the query from then on.

 

[BroncoMarc]

Thanks.. I think I just got it working..

I didn't realize you couldn't have a query and a table with the same name.. Once I tried to save the query with that name and it told me I couldn't use that name because a table existed with that name it all clicked!

Thanks guys..

- Marc