Problem linking to tables in SQL Server 2003 using MS-Access 2000

Hi,

I have an MS-Access database on a shared drive.The Access database has tables linked to a SQL server database.When I try to query the tables in MS-Access database by accessing the share drive,I get the ODBC--call failed error.So I tried creating an ODBC driver for the SQL Server and when I try to link the tables,no tables are displayed.Can anyone help me out with this problem?

-Ruth

If your Access Database has both Code and Data, you should separate them, Put the Data on the shared Drive,

The Code mdb is your client application and can be Linked to SQL-Server thru ODBC

create an ODBC connection to SQL-Server, So Your Client mdb app has both linked Tables to SQL Server and To the Shared Access Database.

You can not link to SQL Server thru an mdb indirectly.

|||

The mdb just has tables linked to SQL Server.I tried creating a ODBC and linking the tables.But it did not work.But when I tried to link to tables in the Northwind database,I am able to do so.I think it is a SQL Server access issue.But I am not sure where exactly the issue lies.

-Ruth

|||

Those are links to the SQL Server table, not the Actual table, so if you want, you can create link to SQL Server by Craeting a ODBC connection from your Control Panel.

Then in your Access app, add linked table using the DSN that you created.

You do not need an mdb on shared drive. if It does not have any local tables.

The once you compiled and distributed you app. On every PC, that it is installed the DSN should be created.

problem linking tables

i can 't add tables to an existing relationship. the identifier of each new table is already existant as foreign key in an existing table called 'etablissement' : when i insert the new table and create the link with table 'etablissement' each table become marked with '*' et when i save i get this message :
"
Error ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]Conflict between
instruction ALTER TABLE and the constraint COLUMN FOREIGN KEY
'FK_etablissement_delegation'. the conflit happened in database
'maintenance', table 'delegation', column 'id_dele'."

nb fields are identic and nonnulls.The table's field where you'd like to set a foreign key(table 'delegation', column 'id_dele') contains an ID that does not exist in the table's field where you'd like to set a PK.

Problem linking tables

I have two tables, TableA and TableB.
Each has a date in smalldatetime format.
TableA has a qty field in int format.
TableB also has a nvarchar field in which the date is in the format
YYYYMMDD

A simple Query on TableA sum Qty returns a result of 100.
However, When I do a left outer join from TableA to TableB on Date, the
sum of Qty on TableA becomes massivley larger.

What could be causing this?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have two tables, TableA and TableB.
> Each has a date in smalldatetime format.
> TableA has a qty field in int format.
> TableB also has a nvarchar field in which the date is in the format
> YYYYMMDD
> A simple Query on TableA sum Qty returns a result of 100.
> However, When I do a left outer join from TableA to TableB on Date, the
> sum of Qty on TableA becomes massivley larger.
> What could be causing this?

Apparently there is more than one row in TableB with the same date
as in TableA.

This should be evident if you replace SUM(qty) with *, so that
you see all rows in the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Please learn that rows are not records and columns are
not fields.

>> TableB also has a nvarchar field in which the date is in the format
YYYYMMDD <<

Why? It is redundant. It is in the wrong data type. Do you actively
seek to make queries run longer and be more error prone? What
constriants do you have on that column to prevent illegal dates?

>> When I do a left outer join from TableA to TableB on Date, the sum
of Qty on TableA becomes massivel larger. <<

DATE is a reserved word in SQL and too vague to be proper data element
name. It will be because the same value appears multiple times in
TableB and you get a CROSS JOIN effect.