Linked tables in Access 2003 from SQL Server 2005 Express

[Phill]

First off, let me say that I'm fairly new to all of this, so it is
probably a beginers mistake.
I have a simple three table database that I have set up in SQL Server
2005 that I want use as a datasource for a simple Access app.
I have a web server setup with SQL Server 2005 running on it. I have
SQL Server Management Studio Express installed on my workstation. I
setup the database with Management Studio on my workstation. I
installed the SQL Server Native Client on my workstation as well. I
created an ODBC data source to connect to the server and then linked
the tables in my Access database (.MDB). When I open the tables in
Access, I can write to them alright. If I add a record, and then go
check the table in Managment Studio, its there. Once I close the table
and reopen it, each field in each record is replaced by #Deleted. The
table's data is still fine in Managment Studio. There appears to be a
read error between SQL Server 2005 and my Access database.

What can I do? Let me know if you need any more information.

Thanks.

 

[Albert D.Kallal]

When you create the table..you need to ensure that the table has a primary
key.

In addition, you ALWAYS want to expose a timestamp field to ms-access also.

The primary key is usually present..as even in a standard mdb back end..99%
of the time you have this.

However, inclusion of the timestamp field is a got-ye....and one you also
want to include...

Add a pk, and timesmap field to the above tables....Delete you links...and
re-link....

 

[Phill]

Thanks for the ideas. I do have a primary key for each table. I added
the timestamp as well, but no luck.

I should add that there is a relationship between the two tables, and
it is defined on the SQL Server. The PK of table 1 has a one to many
relationship to a field on table 2. Table 3 is not related to the
other tables.

Any other ideas?

 

[Rick Brandt]

Thanks for the ideas. I do have a primary key for each table. I
added the timestamp as well, but no luck.

I should add that there is a relationship between the two tables, and
it is defined on the SQL Server. The PK of table 1 has a one to many
relationship to a field on table 2. Table 3 is not related to the
other tables.

Any other ideas?

What DataType is your PK? You want to avoid using any DataType for the PK that
doesn't map exactly to an Acces type and you want to avoid imprecise types as
well.

 

[Phillip Christensen]

What DataType is your PK? You want to avoid using any DataType for the PK that
doesn't map exactly to an Acces type and you want to avoid imprecise types as
well.

Thank you sir, we have a winner. I'm an idiot and decided my primary
keys should be Bigints... Considering this DB will be a few hundred
records at best, I think 2^31 will be more than enough unique values. I
changed them to int, relinked my tables, and it worked.

Thanks again.

 

[Tony Toews]

What DataType is your PK? You want to avoid using any DataType for the PK that
doesn't map exactly to an Acces type and you want to avoid imprecise types as
well.

Good catch. I wouldn't have thought of that one because I would've
assumed everyone would use whatever is equivalent to an Access Long.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Larry Linson]

Good catch. I wouldn't have thought of that one
because I would've assumed everyone would use
whatever is equivalent to an Access Long.

If that sequential identity field were equivalent to an Access Long Integer,
it would limit the Table to a _mere_ Two Gigarecords. <GRIN>