Solved

Access 2000/ MS SQL Server 2000

Posted on 2004-04-30
40
1,050 Views
Last Modified: 2007-12-19
All,

I have a Access 2000/VBA application.  It interfaces with linked tables that are resident on a SQL Server.  The application has been interfacing fine with the linked tables for about a month, but today it has started throwing the following error.

3156  ODBC Delete on a linked table failed.  

Nothing has changed programming wise or database wise to this application.

What I've been doing to resolve the issue is stopping the SQL Server service and restarting it.  After that the application resumes it's normal function.

Anything I can do to fix it rather than starting and stopping the service?
0
Comment
Question by:DSaldanaRELTD
  • 19
  • 17
  • 4
40 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10962989
Can you post the code that causes the delete attempt which faield?  What are your service pack versions?  have those changed recently, or any Windows Updates applied?
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963137
   Set rcsTemp = dbsThisDB.OpenRecordset("SELECT * from bidderslist;")
    With rcsTemp
        .MoveFirst
        Do Until .EOF
            .Delete
            .MoveNext
        Loop
    End With

Service Packs is 4 on all clients.  Windows 2000 Server for the OS on SQL Server.  SP4 too.  No recent updates applied to SQL Server.  Can't verify for clients.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10963219
Why not ust issue a "Delete bidderslist"? instead of looping through every record?  Or, 'Delete bidderslist where somefield = somecriteria"

SQL Server service pack?
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963276
SQL Server Service Pack is 4.  

I hadn't thought of that.  I mean I've looked at it and found it odd that it was failing on the delete and not the select.  

Seemed like some sort of communication error.  I know with SQL server primary keys have to be set on tables in order for updates to take place via linked tables.

I'll try the suggested above code and let you know.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10963358
SQL Server SP 4 does not exist yet that I know of....just for kicks, run Select @@version in a QA session for that server and check it against this list: http://www.aspfaq.com/show.asp?id=2160
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963466
You are right SP3.

Here's the results.

8.00.818 = SP3 / SP3a + KB #821688 (Updated 2004-02-13)
            Also see KB #821277, KB #814950
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963491
Why would the old code cause the error to be thrown?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10963502
OK...cool.  Gotta get the basics done first :-)

If everything was working before...there almost HAS to be something either in the code, permisions or environment that has changed.  Let me know if the Delete works.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10963511
Don't know.  I do my SQL Server work in stored procedures and call them.  I do very little with recordsets such as above :(
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963548
I'm working towards that.  We just upsized our DB from Access to SQL Server.  Next I gotta get these VBA apps updated to VB.

If no more errors are thrown by Tuesday.  I'll assume it worked and reward points then.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10963568
Cool.  I've been Googling, and this error seems like it may be more of an ODBC driver corruption problem.  You may want to check/re-install your drivers.
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963696
How would I go about doing that?
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 334 total points
ID: 10963742
Not sure of the best way, but how about reinstalling MDAC 2.x that you have, or upgrading to whatever the current version is if you are behind?  Do all this on a test system please...I have no idea if this is your issue or not, and don't want you messing up something else...:-)
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10963796
0
 

Author Comment

by:DSaldanaRELTD
ID: 10963852
If the coding change doesn't work, I'll try the MDAC update.  
0
 
LVL 5

Assisted Solution

by:Krys_Wilson
Krys_Wilson earned 166 total points
ID: 10964250
Hello all,

Quick question for DSaldanaRELTD.  

Why not use an Access Data Project (ADP) instead of linking the tables using ODBC?  It would be much quicker and you get a lot of the features of SQL server in your access project.  Its like getting the best of both worlds!

Because you are using SQL linked tables, it wouldn't take to long to upsize the database (There is an access wizard for doing so)  The only problem is if you use a lot of action queries in access (update, make-table, append) they need to be remade as stored procedures.  Views from SQL server only allow select statements but the upside is that SP's are much quicker than a query.

Anyways, its just another of the myriad different ways to do it.

Chris
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10964261
One more thing I forgot to add.  If you do try to make the ADP, make sure and backup your MDB before doing so just in case.

Cheers!

Chris
0
 

Author Comment

by:DSaldanaRELTD
ID: 10977230
What does the ADP format do differently that would be more advantageous than using ODBC and linked tables?  I understand that it is quicker but why.  I will keep this in mind as I am still trying previous ideas.
0
 

Author Comment

by:DSaldanaRELTD
ID: 10977286
Also Kevin3NF I am having this issue on more than one PC.  This is in reguards to updating MDAC.  I will try updating as previous suggestions did not resolve the problem.  But I think it's hard to believe that more than one PC has MDAC corruption although they could be out of date.

Will keep you posted.
0
 

Author Comment

by:DSaldanaRELTD
ID: 10977785
Ok I'm waiting for test to complete on MDAC being a resolution so I'm trying the ADP file.  Now I'm using the same DSN as the link table pulls from but ADP wizard says it can't connect to my SQL Server.

Any ideas?  Again creating link tables using the same DSN works fine.  Very puzzling.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10977916
ADPs use ADO and OLEDB to connect, not DSN and ODBC.  They use the SQL Server engine on the server exclusively for their data processing, not the Jet engine on the local client machine.

ADP is not something you can just throw together in a day...there is a learning curve. :-)

Check the MDAC on teh SQl Server box as well.  Everything I see points to it being an error at the destination.
0
 

Author Comment

by:DSaldanaRELTD
ID: 10978109
MDAC is now the latest release (2.8) on both client and server.  I will continue to evaluate.
0
 

Author Comment

by:DSaldanaRELTD
ID: 10978525
I finally was able to make a ADP file that I'm experimenting with.  It is definitly a different world.  Quick question.  Is the data real-time?  That was the whole point of the linked tables and as they appear in the ADP file, it looks like they are imported.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10978705
Yes it is...its on the server
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10979310
DSaldanRELTD,

Kevin3NF explained it pretty well.

ADP's are real time.  The tables that you see look like imported tables because you are looking directly at the server.  Thats the benefit of using ADP's.

Advantages that I can think of:

* SQL server security on your data.  Jet doesn't have very good security.

* SQL server processing of connections, much much better than Jet can achieve in a multi user environment.

* Ability to use SQL server objects such as Stored Procedures and Triggers.

* Faster processing since the server does all the processing.  This can easily speed up your application since you aren't sending as much through the network.


I really like using ADP's for my database needs.  It takes a little getting used to but they are better client/server than using the Jet engine.

If you decide to go with the ADP's, it looks like Kevin3NF or I can help you set it up.
0
 

Author Comment

by:DSaldanaRELTD
ID: 10980936
One last question on ADP files, can you make MDEs out of them?  I imagine yes but I have not tried.  Does it degrade the performance if you make an MDE out of an ADP?

I guess that's 2 questions.

By the way I'm still evaluating the MDAC solution as the resolution.  
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10980971
No, you make ADEs...but I assume it works pretty much the same.

Performance should not be affected....
0
 

Author Comment

by:DSaldanaRELTD
ID: 10987207
Any Idea on how to upsize this query easily?

SELECT [ID], [Description], [BidDate], [BidTime], [CutOffDate], [CutOffTime], [MFTSectionNum]
FROM letting
WHERE biddate>=[Enter Date Here];

I'm trying to make the query via a view.  The item that is throwing the exception is of course the '[Enter Date Here]' portion of the view.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10987551
Create a stored procedure  via your ADP:

Create Procedure GetLettingRecords
     @BidDate datetime
AS

SELECT [ID], [Description], [BidDate], [BidTime], [CutOffDate], [CutOffTime], [MFTSectionNum]
FROM letting
WHERE biddate>= @Biddate


You need to create a text box somewhere that you can refer to in the Input Parameters of the form.  I don't remember the exact syntax, but something to the effect of : @Biddate = [Forms]![Myform].[Mydatefield]

Access help should be able to help with the Input Parameters piece
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10987711
Correction...

@Biddate datetime = forms!MyForm!txtBidDate

Example of creating a Query By Form form here:

http://support.microsoft.com/?kbid=235359
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10987802
At the bottom of the form properties in an ADP there is a property called Input Parameters.

Place the line "@Biddate datetime = forms!MyForm!txtBidDate" without quotes in there.

Thats one of the main differences between Access and SQL.  Views can only handle straight select statements.  For the full functionality you need to use stored procedures.

Chris
0
 

Author Comment

by:DSaldanaRELTD
ID: 10988669
One last question and I'm set to reward.

I created this procedure below.  Works fine as is, but when I sub in the user inputs it returns nothing.  I'm not sure of the syntax though when embedding these clauses.  I'm embedding like this  WHERE  (madeby= @EmployeeNumber).  I imagine it has something to do with the single quotes but I've tried numberous permutations  like + ''' @EmployeeNumber + '''.  I've also verified the datatypes per the inputs below and they are the same in the table.  Any thoughts?

Alter Procedure [Project Production]
@EmployeeNumber nvarchar,
@FirstDate datetime,
@SecondDate Datetime
AS

SELECT DISTINCT [lettingid], [bookid], [sheets], [pages], [createdon], [emplnamel], [emplnamef]
FROM bidbooks, employeeinfo
WHERE  (madeby= '430' ) And (Bidbooks.CreatedOn BETWEEN '4/1/04' AND '4/30/04') AND (EmployeeInfo.EmployeeNumber = madeby)
ORDER BY [lettingid];
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 334 total points
ID: 10988937
Please only use spaces in names if absolutely required....

@EmployeeNumber needs a length, such as nvarchar(50)

The tables are not properly joined in the FROM or the Where clause...try:
From bidbooks join employeeinfo on bidbooks.madeby = employeeinfo.employeenumber      (correct my field names if I guessed wrong)

or

WHERE  EmployeeInfo.EmployeeNumber = bibbooks.madeby
   and madeby= @employeenumber
   And Bidbooks.CreatedOn BETWEEN @fristdate AND @seconddate


I personally prefer joining in the From clause




0
 

Author Comment

by:DSaldanaRELTD
ID: 10990659
Trying to create the following stored procedure for one of my forms.

Create Procedure "Bid Information for Projects"
@LettingIDcheck nvarchar(18) = Forms!frmLettingIDs!lblLettingID.Caption
AS

SELECT ID, BidDate, BidTime, CutOffDate, CutOffTime, Reminder
FROM letting
WHERE (ID=@LettingIDcheck);

Errors out on this line saying Invalid syntak near '!'.  Must declare @LettingIDcheck nvarchar
@LettingIDcheck nvarchar(18) = Forms!frmLettingIDs!lblLettingID.Caption

Any thoughts?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10990719
Yah.....

Create Procedure BidInformationForProjects
  @LettingIDcheck nvarchar(18)
AS

SELECT ID, BidDate, BidTime, CutOffDate, CutOffTime, Reminder
FROM letting
WHERE (ID=@LettingIDcheck)

RETURN


The = Forms!frmLettingIDs!lblLettingID.Caption doesn't belong.  That goes in the Input Parameters on the ADP form
0
 

Author Comment

by:DSaldanaRELTD
ID: 10990837
OK what's happening now is the prompt is appearing.  Doesn't look like the variable is passing to the stored procedure.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10990933
What prompt is appearing, and when?  
0
 

Author Comment

by:DSaldanaRELTD
ID: 10990973
LettingIDcheck

I run the form, select a project from a list.  A module runs that hashes out the project number and passes it to lblLettingID.  Then a report on that record is opened using the stored procedure (BidInformationForProjects) as it's record source.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 10991038
I would use a txt control instead of a label, and reference it as:

@LettingIDcheck nvarchar(18) = Forms!frmLettingIDs!txtLettingID
0
 

Author Comment

by:DSaldanaRELTD
ID: 10991199
Still the same result.  Here is the VBA code.  I didn't modify the stored procedure.  Again, the report draws from the stored procedure above.  

Private Sub lstLettingList_DblClick(Cancel As Integer)
    txtLettingID.SetFocus
    txtLettingID.Text = lstLettingList.Column(0, lstLettingList.ListIndex + 1)
    DoCmd.OpenReport "Bid Information for Projects", acViewPreview
End Sub

Here's my stored procedure for the report.

Alter Procedure [Bid Information for Projects]
@LettingIDcheck nvarchar(18)
AS

SELECT ID, BidDate, BidTime, CutOffDate, CutOffTime, Reminder
FROM letting
WHERE (ID=@LettingIDcheck)
RETURN
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now