Solved

Run Time error 3151 connecting to sage database

Posted on 2006-11-20
24
1,006 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

13 Experts available now in Live!

Get 1:1 Help Now