Run Time error 3151 connecting to sage database

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
usbcrazy1Asked:
Who is Participating?
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
Its ODBC to Access 2000

how do i check the links?
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
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
 
usbcrazy1Author Commented:
To be honest i copied and pasted the broken Linked table

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

0
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
usbcrazy1Author Commented:
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
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
How do you mean "viewable"?
0
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
Yes.
So where is your application now not working?
What's left that's wrong?
0
 
usbcrazy1Author Commented:
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
 
usbcrazy1Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.