Solved

Run Time error 3151 connecting to sage database

Posted on 2006-11-20
24
1,010 Views
Last Modified: 2008-01-09
Hi

I get a Runtime error 3151 when connecting to a Sage Line50 v7

The error is ODBC Connection to SagelIne 50 failed.

I have checked the network hub wiring and switch, and can ping around the network fine, and access mapped drives fine.

I have my sage ACCDATA on a server and am trying to connect from a client.

I think the VB script might be doing this, but no one has changed any code.

when i press debug  i get a cursor and yellow arrow on the following line marked **
-----------------------------------
Private Sub Form_Current()

   
    Me!OrdDate.SetFocus
    If Screen.ActiveForm.NewRecord = True Then
         Me!Address1 = " "
         Me!Address2 = " "
         Me!Address3 = " "
         Me!Address4 = " "
         Me!Address5 = " "
    Else
        Dim strFilter As String
        strFilter = Me!BillId
   ** Me!Address1 = DLookup("[ADDRESS_1]", "SALES_LEDGER", "[ACCOUNT_REF] = '" & strFilter & "'")**
        Me!Address2 = DLookup("[ADDRESS_2]", "SALES_LEDGER", "[ACCOUNT_REF] = '" & strFilter & "'")
        Me!Address3 = DLookup("[ADDRESS_3]", "SALES_LEDGER", "[ACCOUNT_REF] = '" & strFilter & "'")
        Me!Address4 = DLookup("[ADDRESS_4]", "SALES_LEDGER", "[ACCOUNT_REF] = '" & strFilter & "'")
        Me!Address5 = DLookup("[ADDRESS_5]", "SALES_LEDGER", "[ACCOUNT_REF] = '" & strFilter & "'")
    End If
    Me!Text243 = " "
    Me!Text243.Visible = False
     
    If Me.FilterOn = True Then
        ' Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
        Exit Sub
    End If
   
    If Me!InvNum > 0 Then
        ' Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowEdits = False
        Forms!FOrders!FOrdersSub.Form.AllowEdits = False
        Forms!FOrders!FOrdersSub.Form.AllowAdditions = False
        Forms!FOrders!FOrdersSub.Form.AllowDeletions = False
    Else
        ' Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
        Forms!FOrders!FOrdersSub.Form.AllowEdits = True
        Forms!FOrders!FOrdersSub.Form.AllowAdditions = True
        Forms!FOrders!FOrdersSub.Form.AllowDeletions = True
    End If
   
End Sub
---------------------------------------

Any ideas????

Its really stressing me out this
0
Comment
Question by:usbcrazy1
  • 13
  • 11
24 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17980681
So you have linked tables to the Sage database?
Can you view data through the linked tables directly (i.e. if you open them from the database window)?
0
 

Author Comment

by:usbcrazy1
ID: 17983300
Yes linked tables to the sage database
I cant see the data

All i can see is the shared database that resides
on this machine

Could it still be a network problem?
Sometimes the shares hang, half a second before opening

BUt even when the shares are zippy i get the error?


0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17983789
The tables have linked successfully - but you can't view the data.
Is surely most likely some problem with the drivers for the link.  Is it ODBC - to what format of database?
0
 

Author Comment

by:usbcrazy1
ID: 17986189
Its ODBC to Access 2000

how do i check the links?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986226
Just by opening them from the db window and seeing if you can view data.
Which you say you can't - so that's not good.

Is the actual data source - where the Sage data is stored an Access 2000 mdb?
I'm surprised - although... is v7 actually very old now?

You should be able to create direct table links to another Access database (or any ISAM datasource).
Indeed - ODBC would perhaps even be refused.

If it is an mdb you're aiming at - have you tried just linking directly?
0
 

Author Comment

by:usbcrazy1
ID: 17986234
I have 3 computers...

Computer 1 - Holds the Access Database (Access 2000)
Computer 2 - Holds the (Sage Database Line 50 V7)
Computer 3 - Is a Server (win XP Pro) which holds the mapped Shared Area for the Data


Im think it might be ODBC drivers but i m confused about which drivers need linking to what
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986242
But what is the Sage data stored in?  On Computer 3 - but what format do you link to?
What does the ODBC driver say that you select?
0
 

Author Comment

by:usbcrazy1
ID: 17986248
I have a linked ODBC table to Sage

I have tried to open the table directly and get the

ODBC connection to linked Sage database failed.

so i think you are barking up the right tree, thanks for your help sofar
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986270
When you're creating the table links (create them again now to be sure and so you can see it real time)...

What option do you select?
How do you select the Sage data?
0
 

Author Comment

by:usbcrazy1
ID: 17986282
But what is the Sage data stored in?  On Computer 3 - but what format do you link to?

Its a sage for windows Database on Computer 3 .


What does the ODBC driver say that you select?

Where do i check the driver its linking to? (Control panel, Admin tools, Data sources) on computer 3? or on computer 1 - the computer the main access database is stored
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986293
The driver has to be on Computer 1 - the pc where you're running your application.

Have you tried relinking your tables at all lately?
When you go through the Link Tables process - you have to choose the ODBC source.
The exact choices you make there are of relevance.
0
 

Author Comment

by:usbcrazy1
ID: 17986303
I creted a new link and IT WORKED.....i can see the data in sage from the access database on computer 1

How can a link STOP working????

How do i replace my wrking one with the dud one?

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:usbcrazy1
ID: 17986321
To be honest i copied and pasted the broken Linked table

renamed it, and bingo it worked.....

0
 

Author Comment

by:usbcrazy1
ID: 17986344
When i say it worked , i mean i can now see thw sage data, but i cant link to it,

cos my original link is still throwing errors
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986353
Huh?  "i can now see thw sage data, but i cant link to it"

I don't follow?
Have you created all new links that work - or not? :-S
0
 

Author Comment

by:usbcrazy1
ID: 17986354
When i click on the copied and pasted linked table it asks me for username and password for sage
and then lets me in

When i click on the old link it just says ODBC connection error as if the username and pasword have been tried and failed

Where do i manually enter the username and password into the linked tables ?? so it just goes through
0
 

Author Comment

by:usbcrazy1
ID: 17986367
Huh?  "i can now see thw sage data, but i cant link to it"

Sorry, I mean the sage data tables are now viewable (after copying and pasting the broken linked table, AND entering the username and password at the prompt) , but the access front end doesnt link directly to it,

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986387
How do you mean "viewable"?
0
 

Author Comment

by:usbcrazy1
ID: 17986401
viewable as in i can see the data in their fields, in a big table,

i could only be seeing the data if i could connect to the server, so the link has been established, so ODBC must be working??
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17986419
Yes.
So where is your application now not working?
What's left that's wrong?
0
 

Author Comment

by:usbcrazy1
ID: 17986441
I still get tthe application error

when trying to enter my The database throgh a front end.

I think its to do with access rights or permissions

because the copied and pasted linked table (copied and pasted and renamed from the broken linked table) will allow me to see the data on the server once ive netered the username and password

Im tempted to rename the copied link and try and replace the original one
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17986494
Umm yeah - that would be the natural step.
Your form won't work yet - if it's still looking at the old linked table...
0
 

Author Comment

by:usbcrazy1
ID: 17991865
I renamed the copied linked table back to the name of the original linked table

Access allowed to overwrite the linked table

And the front end works

All i have to do is enter a username and password for sage (added security feature perhaps)
at a prompt...

When you say the form wont work, even if the copied linked table name is exactly the same name as the original?

thanks for all your help LPurvis

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17992343
Once you have linked tables that are viewing the data correctly - and named as the form's are bound to originally (i.e. the same as the tables were beforehand) then the form's should work normally again.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

20 Experts available now in Live!

Get 1:1 Help Now