access / SQL annoyance

yoda · 28 Jan 2008 at 11:25

ok, a friend has passed to me a problem with his access database and his SQL database...
basically their is a data discrepency.... it looks like he's updated one, but not the other....
my problem is....
In the access DB the ID is a number and in SQL it is text so, I can't directly join the tables...

Anyone know a way around this other than changing the ID column in the access DB to text to match the SQL DB?

Thanks

chesterstu · 28 Jan 2008 at 11:31

Is the Text ID field on the sql database only storing numbers or is the letters set for each one and it just updates the number?

yoda · 28 Jan 2008 at 12:01

the SQL has additional records than the access database that use letters....

Manlove is my forte · 28 Jan 2008 at 12:49

yoda said:
the SQL has additional records than the access database that use letters....

But does the field in question have any non-numeric values?

If not you may be able to get away with simply modifying the field definitions.

Otherwise you will have to create a new column and use that as the key.

yoda · 28 Jan 2008 at 12:53

yes the field does... and no I can't change the colums in the SQL server....

What about changing the access database so that the ID is text? the ID field in access is not auto generated...

JIMA · 29 Jan 2008 at 22:24

Hi,

One approach you could take would be to set up a separate table which links the text IDs in the SQL database to the numeric keys in Access (or a newly constructed numeric key where none currently exists).

Downsides are that in order to join the two tables you need to go through this intermediate table, and it's an extra bit of maintenance. Upside is that both existing tables can stay as they are. It's rather clumsy but does minimize impact on the existing data.

IMHO changing the ID of the column in Access to text wouldn't be a good move. Text values aren't (in most cases) good to have as keys due to things such as SQL being case-sensitive (forcing to upper-case in WHERE statements may well mean that the key isn't used). Casting the keys in the WHERE clause of a SELECT e.g casting the numeric Access ID to text, may also result in the key not being used and slow performance. That behaviour would be dependent on the database engine used though.

Changing the keys in the SQL database to be numeric would be the best solution but may not be feasible (but still possible) due to the number of related tables etc. and the amount of testing required to put the change in place.

Hope that helps.

Jim