Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

Access 2000/ MS SQL Server 2000

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
DSaldanaRELTD
Asked:
DSaldanaRELTD
  • 19
  • 17
  • 4
3 Solutions
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
   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
 
Kevin3NFCommented:
Why not ust issue a "Delete bidderslist"? instead of looping through every record?  Or, 'Delete bidderslist where somefield = somecriteria"

SQL Server service pack?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
DSaldanaRELTDAuthor Commented:
Why would the old code cause the error to be thrown?
0
 
Kevin3NFCommented:
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
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
How would I go about doing that?
0
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
If the coding change doesn't work, I'll try the MDAC update.  
0
 
Krys_WilsonCommented:
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
 
Krys_WilsonCommented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
MDAC is now the latest release (2.8) on both client and server.  I will continue to evaluate.
0
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
Yes it is...its on the server
0
 
Krys_WilsonCommented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
No, you make ADEs...but I assume it works pretty much the same.

Performance should not be affected....
0
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
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
 
Kevin3NFCommented:
Correction...

@Biddate datetime = forms!MyForm!txtBidDate

Example of creating a Query By Form form here:

http://support.microsoft.com/?kbid=235359
0
 
Krys_WilsonCommented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
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
 
DSaldanaRELTDAuthor Commented:
OK what's happening now is the prompt is appearing.  Doesn't look like the variable is passing to the stored procedure.
0
 
Kevin3NFCommented:
What prompt is appearing, and when?  
0
 
DSaldanaRELTDAuthor Commented:
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
 
Kevin3NFCommented:
I would use a txt control instead of a label, and reference it as:

@LettingIDcheck nvarchar(18) = Forms!frmLettingIDs!txtLettingID
0
 
DSaldanaRELTDAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 19
  • 17
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now