linke SQL table with auto generated numeric key

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.

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" Wilder wrote in message
...
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.

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" wrote:

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" Wilder wrote in message
...
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.

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" wrote in message
...
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" wrote:

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" Wilder wrote in message
...
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.

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" wrote:

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" wrote in message
...
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" wrote:

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" Wilder wrote in message
...
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.

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" Wilder wrote in message
...
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.

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

"Sylvain Lafontaine" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" Wilder wrote in message
...
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.

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)


"Wilder Lo" wrote in message
...
Thanks a lot, by the way what is the largest Int number I can have?

"Sylvain Lafontaine" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" Wilder wrote in message
...
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.

On Mon, 28 Sep 2009 18:48:02 -0700, Wilder Lo
wrote:

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.

--

John W. Vinson [MVP]