[Access] Write to a SAGE table via ODBC

Question

Hello,   I am new to using Access.  I need to write data from an Access form into a linked SAGE 100 table via ODBC.   I want to write data into the DOC_ENTETE table.  I have no idea how to do that.   Thank you for shedding some light on this.   Can we use recordsets? DAO?

frugierpat · Accepted Answer

Your message doesn't reassure me...  Could a line 500 ODBC driver allow write access?  Thank you

L1L1 · Answer

Hi!  What is line 100?  SQL?  Owner?  Because a 100 "owner" does not have write access via their ODBC driver :o((

frugierpat · Answer

Hi,  it's a proprietary database but I know we can write into it. Our IT provider suggested a solution (too expensive). It's possible but I don't actually know how.  Thanks frugierpat

L1L1 · Answer

Hello!  Well, actually, what's not possible is to do it with the ODBC driver sold by Sage to the end client... Maybe the distributors have more powerful tools?!?  Best regards,

L1L1 · Answer

Hello !!  Well, sorry to not reassure you, but ... no :o((  The driver for line 500 is also a read-only driver!  No way to replace/update the data!!  The latest versions (100, 1000 ...) are "SQL Server" versions and there (almost) anything is allowed...  What is your project? There might be another way...  Best regards,

ppa · Answer

Hi Frugierpat,  To write to the tables in the cBase database, which is proprietary, it is essential to use the Sage Odbc driver.  This specific driver automatically handles simultaneous access conflicts (lock, unlock on records and files) and takes care of the links between tables to ensure data consistency.  For example, when creating a movement in F_DOCENTETE and F_DOCLIGNE, the driver automatically manages stock, which is particularly complicated in line 100.  Even under SQL, while one could write directly to the database, it is strongly recommended to use this Odbc driver.  For example, I created an auto interface that integrates orders received continuously into the gescom from a specific pharma system.

marcelin · Answer

I am curious to know how you connect to the Sage database?  I am trying to write in SAGE accounting 100. I have the ODBC driver provided by SAGE.  I can view the tables, but when importing, an error message informs me that there is an argument problem (on Access) (another message on windev9).  How do you succeed?

Fred · Answer

Hello everyone Well, I have several issues with ASP and Sage ODBC First, I have a message saying 'wrong file version' I'm on 12.02 for ODBC and I have databases in version 12 and in version 13 Do I need to run ODBC on the machine where the Sage server is located or can I open my databases on another machine (while I work on my program) Then I have issues with instructions that don't work in ASP For example: Dim Connection As Connection or "Set Connection = wrkODBC.OpenConnection" So maybe I'm missing a file in the program to define my DLLs like  So the program that is a bit further up doesn't work for me Help !!!!!

tipierre · Answer

Does anyone know the name of the table where all the information about a client is stored?

David · Answer

Hello,  I have a Sage Ligne 100 version with ODBC drivers. I can perfectly access the tables of the Commercial Management software, but when I try to access the tables for the accounting software, I unfortunately cannot open them; I get an error message that says (Invalid argument) for any accounting table.  Could you help me with this?  Thank you in advance to everyone.

marcanto65 · Answer

Hello everyone I am using version 14.04 of Sage and the corresponding Odbc driver. I have a small issue with my SQL query. I'm trying to transfer data from Excel to Sage, but it's not going well. It returns me the following error message: [Simba][Simba ODBC Driver]Update F_ARTICLE Set AR_CODEEDIED_CODE1 = 3270190218289 AR_POIDSNET<>=100 Where AR_REF='BRAAR10' Do you have any idea? Thank you in advance Marc

mika903 · Answer

It's fine, you already have 48 comments, so please stop bothering us, thank you.

wolf · Answer

Hello, Excuse my poor French. We are developing software that accesses a Sage Ligne 100 commercial management database using the Sage ODBC driver. At the moment, it is not possible to connect when Sage commercial management is open at the same time. The access with the ODBC driver works fine when the commercial management is closed. Is it possible to open the database non-exclusively? We do not want to write just search queries.  Thank you very much for your help  Wolfgang

David · Answer

Hello Mozart,  Do you have the SAGE server installed on your workstation that allows the simultaneous opening of the same commercial management file? (Can you open multiple commercial management files at the same time?)  David

Mike · Answer

Hello,  I am currently creating a query to get the purchase price of an item to add it to the standard cost. By default, the F_ARTPRIX table is empty, so I need to insert values with the corresponding AR_REF (I first select the values from the F_ARTICLE table, then in my while loop I perform the insert of the returned values if I don't already have a record with the same reference).  Anyway, I am encountering an error message as follows: execution error '-2147217913 (80040e07)': [Simba][Simba ODBC Driver]Error in assignment  and during debugging, it points to my cnx.Execute Sql  I am freaking out because I don't see how to resolve this issue at all. Thanks to anyone who might have an answer (I know it's not easy). If necessary, I can paste the code, but I don't think it's needed.  Thanks!!!

ppa · Answer

Hello Mike,It seems that your ODBC driver is complaining about an error related to trying to assign a value of the wrong type to a field in the table.So go ahead and copy/paste your SQL code that you submit to the driver, so we can take a look at the syntax.Talk to you later,Phil

Mike · Answer

Here is the stash ;) Thanks for taking a look! :)  Sub test()  'Variable declaration Dim cnx As ADODB.Connection Dim rst As ADODB.Recordset Dim verif As ADODB.Recordset  'Variable instantiation Set cnx = New ADODB.Connection Set rst = New ADODB.Recordset Set verif = New ADODB.Recordset  'Database connection cnx.ConnectionString = "DSN=test;UID=plop;PWD=plop;" cnx.Open  'Executing the query rst.Open "SELECT AR_REF, AR_PRIXACH FROM F_ARTICLE", cnx rst.MoveFirst  While Not (rst.EOF)   Dim Sql As String  Dim value As Variant  Dim ref As String  value = rst("AR_PRIXACH")  ref = rst("AR_REF")    'Executing the query  verif.Open "SELECT AR_REF FROM F_ARTPRIX WHERE AR_REF='" & ref & "'", cnx   Dim nbRst As Variant  nbRst = verif.CacheSize    If nbRst <> 0 Then   Sql = "INSERT INTO F_ARTPRIX (AR_COUTSTD, AR_REF)"  Sql = Sql & " VALUES "  Sql = Sql & "('" & value & "', "  Sql = Sql & "'" & ref & "')"   'On Error Resume Next  'debugger says that the error is here??? is it my insert or the values of my insert?  cnx.Execute Sql   Else  MsgBox ref & " already entered "  End If   rst.MoveNext Wend End Sub

ppa · Answer

Hello,   Sql = Sql & "('" & value & "', "   A priori, the &value& is enclosed in single quotes which implies a value of type "character", whereas AR_COUTSTD is of type "Numeric"   Give it a try and let us know the result of your correction.    See you later   phil

Mike · Answer

Hello,   even if I declare value as a String it does not work maybe it is necessary to force the conversion of rst("AR_PRIXACH") into String format is there a function that allows this? unless the Sage 100 database only accepts certain specific types?  Thx

laurent · Answer

and how to access the rights table for editing  control   since we do not have access to it....

ppa · Answer

Hello Laurent,

In the proprietary products, the access rights tables are not accessible for security reasons.
Therefore, accounts and passwords must be managed manually, knowing that a password can still be reset by the administrator or any duly authorized person.

It is important to take this record today, because with the update to V15, the list will no longer even be accessible for consultation.

See you later

Phil

deepstarf · Answer

hello :)  I recently started using Sage and I would like to export the database to Access or MySQL, can anyone help me please  thank you in advance :)

neolaurent · Answer

Thank you, but unfortunately that doesn't address the fact that someone might have found a trick to read (edit) the access rights list

Indeed, we have a lot of rights by group, etc. and it's very tedious to manually enter this into Excel sheets...

with all the risks of error and the impossibility to check if an administrator has come to modify the rights that have been validated by the company

In fact, if we could only read, even with a method that is not very "orthodox," in order to compare with our external database of allocated rights, that would be great.

bila · Answer

Hello,I arrived a little late to this forum, but I am also interested in this .pdf file (indicating the structure of the Sage L100 files) if you could send me the document.

bile · Answer

Hello, Thank you for the ppa link.

Mehdi · Answer

Hello, could someone provide the ODBC Sage version 13.01? I have it, but my CD is damaged :(  Thank you very much.

Houssem86 · Answer

I want to execute a query that contains the SQL Replace function via ODBC, but I'm getting this error: [Simba][Simba ODBC Driver]Invalid scalar function: REPLACE.  Do you have a solution?

[Access] Write to a SAGE table via ODBC - Page 2

The ctrl key works intermittently

Antivirus problem

Wifi connection issue

Connecting a sony handycam video 8 ccd f555e to an hd tv

Hello sir

Incompatible display driver

Sensitive sites blocking tool

Ink absorber

Phone battery life

Pin code (windows hello)