Hi,

I am trying to test SQL Server 2000 tables which I have linked into an Access 97 database.

If I use the normal DAO connections, I can access the records on a read only basis.

SQLServerTest1 is the name of the DSN created to connect to the actual SQL Server 2000 server, PM is the name of the database within the given SQL Server, q_temp is the name of the table within SQL Server (and the name of the linked table)

would anyone be so kind as to advise the correct procedure to open this type of table for editing from within Access 97. - and point me in the right direction on the web for any articles, tutorials etc.
-

most of those I have looked at are using sql server linked to access 2000 (not access 97)

many thanks

LU

Show your code



Sal

Hi Sal,

After posting, I continued to search the net and found an article advising that if Key fields were not identified then tables would be read-only ...... felt rather silly as I has simply imported a few test tables into SqlServer2000 and linked them back to Access 97, then spent ages testing various types of connection code when the problem was something else.

I went back int SqlServer and added the primary keys, relinked the tables (or refreshed the links) and it all worked .....

It has however, thrown up a different problem .. not sure if anyone in the forum has experienced it.

30 tables have been exported to SqlServer (single or multiple primary keys added) and relinked back into Access97.

For some reason, 2 tables show as ODBC failure .... and only if I create a primary key (on a text field) in Sql Server. If I remove the key and refresh the link, the data is visible, when I add the key and refresh the link - ODBC call failure... I have tried deleting and relinking the tables with the key already created - still fails - but if I once again remove the key, refresh and data is visible (read-only).


  all the other tables are fine... am at a loss as what to do next as these tables are major tables which will require data to be added.

many thanks

LizU

First off, if you are connecting via ado do not use currentproject.connection or the dao equivalent. there is no point to using SQL Server if you will be using linked tables.

Drop all of your linked tables from the Access front end and re-link them all. I have ween this happen and this may fix it.



Sal

Thanks Sal,

The reason for moving tables to Sql Server is the limit on the size of the access database and that sql server can be backed up without the database being closed. I removed all embedded and linked tables from the Access database and linked in all the sql server tables, but for some reason these 2 tables refuse to allow me a primary key ... but I will kepp on trying.

LizU

go to enterprise manager and create a script of the table in reference and paste the code for the table.



Sal

Hi Sal,

here is the script for the "company" table, followed by the "contract" table.

many thanks

Liz

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO

CREATE TABLE [dbo].[Company] (
    [Premis Code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Company Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Contact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Position] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Telephone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Contract Period] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Contract Period Gas] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Module1] [bit] NULL ,
    [Module2] [bit] NULL ,
    [Module3] [bit] NULL ,
    [Module4] [bit] NULL ,
    [Module5] [bit] NULL ,
    [Module6] [bit] NULL ,
    [Module7] [bit] NULL ,
    [Module8] [bit] NULL ,
    [Module9] [bit] NULL ,
    [Module10] [bit] NULL ,
    [Renewal Date] [datetime] NULL ,
    [Last Invoice] [float] NULL ,
    [Overall Objective] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Electricity] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Gas] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Base Date] [datetime] NULL ,
    [CompReg] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [bolWebSupplierAvailable] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
    CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
    (
        [Premis Code]
    ) ON [PRIMARY]
GO

and .....

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contract]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contract]
GO

CREATE TABLE [dbo].[Contract] (
    [PREMIS CODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SITE CODE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SITE NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SITE ADDRESS 1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SITE ADDRESS 2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TOWN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COUNTY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [POSTCODE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BILL NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BILL ADDRESS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BILL TOWN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BILL COUNTY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BILL POSTCODE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TYPE OF BUSINESS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [METERS] [float] NULL ,
    [PROPERTY OCC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PROPERTY OPER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PROPERTY WORK] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PROPERTY WORKYR] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SHIFT PATTERN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [REGION] [float] NULL ,
    [SITETYPE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FLOOR] [float] NULL ,
    [ELEC SUPPLIER] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ELEC VOLTAGE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [REC] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [REC TARIFF] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [REC ACCOUNT NO] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NGC ZONE] [float] NULL ,
    [MPAN Core] [varchar] (27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Memo1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Memo2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Notes1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Notes2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Notes3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Memo1Gas] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Memo2Gas] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Max Dem] [float] NULL ,
    [Capacity] [float] NULL ,
    [Supply Voltage] [float] NULL ,
    [Gas Meter Operator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Gas Meters] [float] NULL ,
    [Gas Meter Numbers] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Transco Pcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LDZ] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Pressure System] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [M Number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Module 1] [bit] NOT NULL ,
    [Module 2] [bit] NOT NULL ,
    [Module 3] [bit] NOT NULL ,
    [Module 4] [bit] NOT NULL ,
    [Module 5] [bit] NOT NULL ,
    [Module 6] [bit] NOT NULL ,
    [Module 7] [bit] NOT NULL ,
    [Module 8] [bit] NOT NULL ,
    [Module 9] [bit] NOT NULL ,
    [Module 10] [bit] NOT NULL ,
    [PRESSURE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BGAS REGION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GAS TARIFF] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GAS SUPPLIER] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NOMGAS] [float] NULL ,
    [MDAYGAS] [float] NULL ,
    [MAXGAS] [float] NULL ,
    [Meter Manufacturer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Meter Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Meter Badge Capacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Checked] [bit] NOT NULL ,
    [Memo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Gas Group] [smallint] NULL ,
    [ClientCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contract] WITH NOCHECK ADD
    CONSTRAINT [PK_Contract] PRIMARY KEY CLUSTERED
    (
        [SITE CODE]
    ) ON [PRIMARY]
GO

Creating a primary key with WITH NOCHECK suggests to me that you added records first and then you did not ensure that the primary key had unique values before. Check for duplicates on that column, if you have them, drop duplicates or find an alternative primary key.

I bet you do have duplicates.




Sal

thanks Sal,

I will check .. you never know ... the same key was teh primary key in the Access database so should have been ok
many thanks for your help.

Liz

If there is even one record as duplicate, the tables will not link correctly. Just do a select distinct on that column and look at the count. If it is anything less than the total of records on the table, that would be your problem.



Sal