Link to home
Start Free TrialLog in
Avatar of usbcrazy1
usbcrazy1

asked on

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
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

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)?
Avatar of usbcrazy1
usbcrazy1

ASKER

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?


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?
Its ODBC to Access 2000

how do i check the links?
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?
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
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?
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
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?
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
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.
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?

To be honest i copied and pasted the broken Linked table

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

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
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
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
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,

How do you mean "viewable"?
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??
Yes.
So where is your application now not working?
What's left that's wrong?
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
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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.