linke SQL table with auto generated numeric key

[Wilder Lo]

In Access, how can I link a SQL table with a numeric key generated by SQL?
I've tried to do that but Access will take the field as text and cannot
retrieve the data from the data.

 

[Sylvain Lafontaine]

Access is compatible with some but not all numeric types of SQL-Server. For
example, INT works but not BIGINT. The presence of an Insert trigger that
will perform a second insertion into another table (for example in an audit
process) will also be the source of big problem.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Wilder Lo]

Is there any way to bypass this? Or is there another way to do something like
the auto generated key in SQL and accessible by ACCESS?

 

[Sylvain Lafontaine]

I don't know as you don't have provided any detail about your exact problem:
what is this numeric type and how its value for a new record is generated at
this moment. You should also say if this field is defined as the primary
key for the table on the SQL-Server and otherwise, if there is any unique
index for it.

You also don't say what you mean exactly with the expression "Access will
take the field as text and cannot retrieve the data from the data".

Finally, maybe that you have simply forgot to refresh (or recreate) the ODBC
Link after making some change to the sql-server table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Wilder Lo]

Here are the details:

On the SQL server I have a table with single key field of "bigint" data type
and auto increment.

I've linked the table in Access through ODBC, and choose the key field as
primary key, when I open up the table to see the data, all I can see is
"#Deleted" in every field and record. I've tried to remove the ODBC link and
re-link but still doesn't work.

 

[Sylvain Lafontaine]

You're not a lucky guy: the BigInt type is not compatible with Access, as
simple as that. You have no other choice than to change this type to
something else like Int.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Wilder Lo]

Thanks a lot, by the way what is the largest Int number I can have?

 

[Sylvain Lafontaine]

A little higher than 2 billions: > 2 000 000 000.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John W. Vinson]

Thanks a lot, by the way what is the largest Int number I can have?

2147483647; and if you count negatives, from -2147483648 up to zero.

I once figured out how long it would take to use up all the Long Int keys.
Adding one record per second, 24-7, no time off for holidays, it comes to a
bit over 68 years.