Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 4th, 2004, 08:14 AM
Registered User
 
Join Date: Oct 2003
Location: Newmarket, Cambridgeshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access and update linked SQL Server 2000 table

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

Reply With Quote
  #2 (permalink)  
Old May 4th, 2004, 02:11 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Show your code



Sal
Reply With Quote
  #3 (permalink)  
Old May 6th, 2004, 06:17 AM
Registered User
 
Join Date: Oct 2003
Location: Newmarket, Cambridgeshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Reply With Quote
  #4 (permalink)  
Old May 6th, 2004, 09:03 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old May 7th, 2004, 09:12 AM
Registered User
 
Join Date: Oct 2003
Location: Newmarket, Cambridgeshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old May 7th, 2004, 01:15 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
Reply With Quote
  #7 (permalink)  
Old May 10th, 2004, 05:34 AM
Registered User
 
Join Date: Oct 2003
Location: Newmarket, Cambridgeshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #8 (permalink)  
Old May 10th, 2004, 09:20 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old May 10th, 2004, 11:17 AM
Registered User
 
Join Date: Oct 2003
Location: Newmarket, Cambridgeshire, United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old May 10th, 2004, 12:42 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Locking in Access DB linked to SQL Server backend gibbers Access 3 April 24th, 2007 03:20 PM
Access MDB with a Linked SQL Server Table..... timmaher Classic ASP Databases 0 September 28th, 2005 03:57 AM
generate XML from SQL server 2000 table sasidhar79 XML 2 August 3rd, 2005 11:05 AM
looking for access 2000 to sql server 2000 sql/que method SQL Server 2000 0 July 7th, 2005 12:46 PM
SQL SERVER 2000 AND ACCESS 2000 ckentebe SQL Server 2000 3 June 17th, 2004 08:50 PM



All times are GMT -4. The time now is 09:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.