[Access] Write to a SAGE table via ODBC
frugierpat
Posted messages
4
Status
Member
-
Houssem86 -
Houssem86 -
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?
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?
Configuration: XP Pro Access 97
27 answers
- 1
- 2
Next
Hi!
What is line 100?
SQL?
Owner?
Because a 100 "owner" does not have write access via their ODBC driver :o((
What is line 100?
SQL?
Owner?
Because a 100 "owner" does not have write access via their ODBC driver :o((
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
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
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,
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,
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,
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,
Hi,
I found a solution, I don't know if it's very clean since I'm not a trained programmer:
Dim Connection As Connection
Dim wrkODBC As Workspace
Set wrkODBC = CreateWorkspace("Name", "User", "Password", dbUseODBC)
Set Connection = wrkODBC.OpenConnection("ODBC;DATABASE=name;UID=User;PWD=Password;DSN=Test Connection")
Set rs2 = Connection.OpenRecordset("UPDATE F_DOCENTETE SET DO_COORD04 = '" & Left(TxtRecepisse.Value, 8) & "' WHERE DO_TYPE = 3 and DO_PIECE = '" & Left(TxtBLencours.Value, 8) & "'", dbOpenDynaset)
Do While rs2.StillExecuting
Debug.Print " [in progress...]"
Loop
Connection.Close
Even with the document open in SAGE, I have no conflict. It's just a write to free fields.
I'm testing and retesting but it works for now.
Best regards,
Patrick
I found a solution, I don't know if it's very clean since I'm not a trained programmer:
Dim Connection As Connection
Dim wrkODBC As Workspace
Set wrkODBC = CreateWorkspace("Name", "User", "Password", dbUseODBC)
Set Connection = wrkODBC.OpenConnection("ODBC;DATABASE=name;UID=User;PWD=Password;DSN=Test Connection")
Set rs2 = Connection.OpenRecordset("UPDATE F_DOCENTETE SET DO_COORD04 = '" & Left(TxtRecepisse.Value, 8) & "' WHERE DO_TYPE = 3 and DO_PIECE = '" & Left(TxtBLencours.Value, 8) & "'", dbOpenDynaset)
Do While rs2.StillExecuting
Debug.Print " [in progress...]"
Loop
Connection.Close
Even with the document open in SAGE, I have no conflict. It's just a write to free fields.
I'm testing and retesting but it works for now.
Best regards,
Patrick
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.
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.
Hello PPA !!
Hello Forum !
I'm facing the same difficulty, I'm working with Cbase and I would like to extract data from SAGE to insert it into a database built on MySQL (and vice versa)
As you know, cBASE is proprietary so I need an ODBC to manipulate the different SAGE tables
Thank you for informing me on how to download a compatible ODBC for SAGE 100 (version 15)
James .........
Hello Forum !
I'm facing the same difficulty, I'm working with Cbase and I would like to extract data from SAGE to insert it into a database built on MySQL (and vice versa)
As you know, cBASE is proprietary so I need an ODBC to manipulate the different SAGE tables
Thank you for informing me on how to download a compatible ODBC for SAGE 100 (version 15)
James .........
Hello Paty
You're absolutely right that Sage in a proprietary database requires an ODBC driver to access data for both reading and writing.
The Sage ODBC driver doesn't just provide an access interface, but it also ensures essential consistency checks for the integrity of the Cbase database. Therefore, the ODBC driver is not freely downloadable like MySQL might be, as it incorporates business rules specific to applications developed by Sage.
The Sage L100 ODBC driver (also compatible with L30) is thus sold by authorized Sage resellers at a retail price of €300 excluding VAT, a price that hasn't changed in several years despite the enhancement of business rules.
I hope I have answered your request.
Best regards,
Phil
--
Wisdom is not a gift. It is learned, patiently, by listening to those around us and by retrospectively assessing the matters at hand. Let us therefore become Sage.
You're absolutely right that Sage in a proprietary database requires an ODBC driver to access data for both reading and writing.
The Sage ODBC driver doesn't just provide an access interface, but it also ensures essential consistency checks for the integrity of the Cbase database. Therefore, the ODBC driver is not freely downloadable like MySQL might be, as it incorporates business rules specific to applications developed by Sage.
The Sage L100 ODBC driver (also compatible with L30) is thus sold by authorized Sage resellers at a retail price of €300 excluding VAT, a price that hasn't changed in several years despite the enhancement of business rules.
I hope I have answered your request.
Best regards,
Phil
--
Wisdom is not a gift. It is learned, patiently, by listening to those around us and by retrospectively assessing the matters at hand. Let us therefore become Sage.
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?
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?
Good evening Marcelin, good evening Forum,
I don’t master Access, but here is the method to connect via Excel. In the ODBC data source management, after installing the Sage ODBC driver, in the "System DSN" tab, click the "Add" button, select the "Sage Business Management" driver. Name the data source (e.g., GC_TEST), then click the "Open" button, select the accounting file (e.g., C_TEST.MAE), click the "Open" button again, select the Gescom file (e.g., G_TEST.MAE). Validate the ODBC link.
In Excel, go to "Data," "External Data," "Create a New Query." Ms Query must be installed and the window provides access to the list of ODBC links, select GC_TEST.
If the Sage database is protected by passwords, enter the requested information. Ms Query opens the database and provides access to the tables and fields.
For Windev, use the function
SqlConnecte("GC_TEST", REQ1, "", "ODBC")
cCommande = "select * from F_DOCENTETE where ...)
Sql Execute(REQ2, cCommande)
Before exiting the application, execute "SqlDeconnecte(REQ1)
The Windev documentation is well done and everything is explained there.
Good luck
Phil
I don’t master Access, but here is the method to connect via Excel. In the ODBC data source management, after installing the Sage ODBC driver, in the "System DSN" tab, click the "Add" button, select the "Sage Business Management" driver. Name the data source (e.g., GC_TEST), then click the "Open" button, select the accounting file (e.g., C_TEST.MAE), click the "Open" button again, select the Gescom file (e.g., G_TEST.MAE). Validate the ODBC link.
In Excel, go to "Data," "External Data," "Create a New Query." Ms Query must be installed and the window provides access to the list of ODBC links, select GC_TEST.
If the Sage database is protected by passwords, enter the requested information. Ms Query opens the database and provides access to the tables and fields.
For Windev, use the function
SqlConnecte("GC_TEST", REQ1, "", "ODBC")
cCommande = "select * from F_DOCENTETE where ...)
Sql Execute(REQ2, cCommande)
Before exiting the application, execute "SqlDeconnecte(REQ1)
The Windev documentation is well done and everything is explained there.
Good luck
Phil
Hello, I would also like to create a Windev interface that can access the database of Sage commercial management 100. In fact, I need to generate files in EDI format. I don't have the ODBC driver, but I would like to make sure I can do everything I need before purchasing it. Can you confirm that with this driver I have read/write access to the entire Sage database?
Could someone send me this driver so I can test it?
Thank you in advance.
Could someone send me this driver so I can test it?
Thank you in advance.
Good evening Rob1f, good evening Forum,
The ODBC driver used to access Sage databases is a Simba driver. It provides access to all databases for read and write operations while maintaining data integrity. For example, it is impossible to generate a DELETE query on an item if it has stock, serial or lot numbers, or if it is involved in any document. However, the driver itself ensures the consistency of the information (e.g., for a request to create an incoming stock movement, it checks if the item is stock-managed, ensures automatic calculation of projected and actual stocks, and verifies if the item is tracked by serial/lot/FIFO to refuse the movement if it lacks a lot/serial number).
Furthermore, the tables for managing user accounts and access rights remain inaccessible.
To access the databases, you must indicate a valid login and password in the connection request (sqlconnect()).
The ODBC driver must be compatible with the version of the applications. It is sold with an activation key without restrictions, and thus should not be shared for testing without falling under the scope of a pirated license, which does not align with the ethics of this forum.
If needed, feel free to contact me through the forum for more information.
See you later
Phil.
The ODBC driver used to access Sage databases is a Simba driver. It provides access to all databases for read and write operations while maintaining data integrity. For example, it is impossible to generate a DELETE query on an item if it has stock, serial or lot numbers, or if it is involved in any document. However, the driver itself ensures the consistency of the information (e.g., for a request to create an incoming stock movement, it checks if the item is stock-managed, ensures automatic calculation of projected and actual stocks, and verifies if the item is tracked by serial/lot/FIFO to refuse the movement if it lacks a lot/serial number).
Furthermore, the tables for managing user accounts and access rights remain inaccessible.
To access the databases, you must indicate a valid login and password in the connection request (sqlconnect()).
The ODBC driver must be compatible with the version of the applications. It is sold with an activation key without restrictions, and thus should not be shared for testing without falling under the scope of a pirated license, which does not align with the ethics of this forum.
If needed, feel free to contact me through the forum for more information.
See you later
Phil.
Hello rob1f, the forum,
The ODBC driver can be obtained from a Sage reseller under the reference ODB10WPP100 at a fixed price of €300.
This product does not come with a maintenance contract. However, it is essential to provide the version number of your gescom in order to get the compatible driver.
There is no trial version available for the driver.
For customer contact at Sage, 01.41.66.21.21.
If needed, feel free to contact me through the forum for more info, or leave me your email address.
See you soon
Phil.
The ODBC driver can be obtained from a Sage reseller under the reference ODB10WPP100 at a fixed price of €300.
This product does not come with a maintenance contract. However, it is essential to provide the version number of your gescom in order to get the compatible driver.
There is no trial version available for the driver.
For customer contact at Sage, 01.41.66.21.21.
If needed, feel free to contact me through the forum for more info, or leave me your email address.
See you soon
Phil.
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 !!!!!
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 !!!!!
Hi Fred,
I can answer you about Sage but not about asp.
1 - The ODBC driver must match the version of the database you are accessing. Be careful, you cannot install Sage ODBC drivers in two different versions simultaneously. Your driver must be installed on the machine running the application.
2 - The Sage server is used to ensure concurrent user access to the database (including ODBC access). If you don't go through the Sage client/server, you will open the databases exclusively and halt the operation. An elegant solution would be to copy your databases to the dev machine, and then you can bypass the Sage server.
Good luck, see you later
Phil
I can answer you about Sage but not about asp.
1 - The ODBC driver must match the version of the database you are accessing. Be careful, you cannot install Sage ODBC drivers in two different versions simultaneously. Your driver must be installed on the machine running the application.
2 - The Sage server is used to ensure concurrent user access to the database (including ODBC access). If you don't go through the Sage client/server, you will open the databases exclusively and halt the operation. An elegant solution would be to copy your databases to the dev machine, and then you can bypass the Sage server.
Good luck, see you later
Phil
Hi Tipierre,
In Sage Ligne 100, client information is recorded in a third-party file (clients, suppliers, employees, and others). The third-party account is identified in the F_COMPTET table, which has links to, among others, the F_COMPTEG table (for collective general account control) and F_COMPTETG (general accounts for multi-collective).
The identification of a client third-party is done through the F_COMPTET.CT_Type zone = 1.
These tables are common to both accounting and commercial management, for which we simultaneously open the MAE and GCM databases for the use of gescom.
I'm at your disposal for more information.
Phil
In Sage Ligne 100, client information is recorded in a third-party file (clients, suppliers, employees, and others). The third-party account is identified in the F_COMPTET table, which has links to, among others, the F_COMPTEG table (for collective general account control) and F_COMPTETG (general accounts for multi-collective).
The identification of a client third-party is done through the F_COMPTET.CT_Type zone = 1.
These tables are common to both accounting and commercial management, for which we simultaneously open the MAE and GCM databases for the use of gescom.
I'm at your disposal for more information.
Phil
Hello,
I would like to execute a delete or update on the Sage 100 database
but I get the following message:
<eb1>Update statement not supported.
State:37000,Native:0,Origin:[Simba][SimbaEngine ODBC Driver]</eb1>
I don't understand!!!
I would like to know what I need to do.*
Also, do you happen to have a document that can help me with the use of DUMP on Sage?
I would like to execute a delete or update on the Sage 100 database
but I get the following message:
<eb1>Update statement not supported.
State:37000,Native:0,Origin:[Simba][SimbaEngine ODBC Driver]</eb1>
I don't understand!!!
I would like to know what I need to do.*
Also, do you happen to have a document that can help me with the use of DUMP on Sage?
Hello everyone,
I've already posted this question that I'm trying again.
When I import a table from Sage 12.02 via the ODBC driver 12.02 Samba on XP Home SP2, the list of tables in the accounting software is displayed normally, however when I select a table and then click OK, I get the message: "Invalid argument."
After searching the Internet, it seems that this is due to the fact that the version of msjet40.dll is too recent (4.0.8618.0). This corresponds to SP8 of the Jet engine.
It seems that I need to install Jet engine 4.0 SP6. But I can't find it on the net.
Can someone help me solve this problem? Thank you in advance.
Pandarouge.
I've already posted this question that I'm trying again.
When I import a table from Sage 12.02 via the ODBC driver 12.02 Samba on XP Home SP2, the list of tables in the accounting software is displayed normally, however when I select a table and then click OK, I get the message: "Invalid argument."
After searching the Internet, it seems that this is due to the fact that the version of msjet40.dll is too recent (4.0.8618.0). This corresponds to SP8 of the Jet engine.
It seems that I need to install Jet engine 4.0 SP6. But I can't find it on the net.
Can someone help me solve this problem? Thank you in advance.
Pandarouge.
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.
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.
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
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
Hello marcanto65,
Your request would seem correct if you added a " , " before AR_POIDSNET.
The sign <> indicates the element that the request cannot interpret, and in this case, it's the separation with 3270190218289.
Moreover, what is the type of AR_CODEEDIED_CODE1? Wouldn't it be alpha? In that case, you should put your value between " ' " (single quote).
Good luck,
See you soon
Phil
Your request would seem correct if you added a " , " before AR_POIDSNET.
The sign <> indicates the element that the request cannot interpret, and in this case, it's the separation with 3270190218289.
Moreover, what is the type of AR_CODEEDIED_CODE1? Wouldn't it be alpha? In that case, you should put your value between " ' " (single quote).
Good luck,
See you soon
Phil
Hi mika903,
Who are you targeting in your post?
The users who encounter problems don't bother me, personally.
Not everyone can be an expert in every field.
Well, on the other hand, I'm not the type to send 15 posts in 2 days either.
“Tolerance” could be a necessary virtue on a support forum.
May your day be pleasant and beneficial for your peace of mind...
See you later
Phil
Who are you targeting in your post?
The users who encounter problems don't bother me, personally.
Not everyone can be an expert in every field.
Well, on the other hand, I'm not the type to send 15 posts in 2 days either.
“Tolerance” could be a necessary virtue on a support forum.
May your day be pleasant and beneficial for your peace of mind...
See you later
Phil
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
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
Good morning wolf,
Bonjour wolf,
The software Sage L100 accesses the database directly. So, a lock exclusively locks the tables.
To share access to the database, you need to use a client/server part called "Server NT Sage". It works with NT, 2K, 2K3. Be careful with Vista. The first part (and the second one) are installed on the server, the second part is on each user workstation. The protocol used to communicate is TCP/IP.
The ODBC driver accesses through C/S mode, and you can share the database with the Sage application (on the same workstation or another one). Of course, you need to have a multiuser license for the "commercial management". You can obtain the Sage C/S from a Sage reseller. The cost is about 300 €.
----------
Le logiciel Sage L100 accède à la base de données directement. Un verrou bloque les tables de façon exclusive.
Pour partager l'accès à la base de données, tu dois utiliser un module "client serveur" nommé "Serveur Sage pour NT". Ça fonctionne également avec NT, 2000, 2003. Attention avec Vista. La première partie (et la seconde) sont installées sur le serveur, la seconde sur chaque poste utilisateur. Le protocole utilisé est TCP/IP.
Le driver ODBC accède en C/S et tu peux partager ta base de données avec l'appli Sage (sur le même poste ou sur un autre). Bien sûr, tu dois disposer d'une licence multiutilisateurs pour ta gestion commerciale. Tu peux obtenir ce Serveur Sage auprès d'un revendeur au prix d'environ 300 €.
You can email me if you need any further information.
Tu peux m'envoyer un mail si tu as besoin d'autres informations.
Good luck, wolf,
bonne chance,
Phil
Bonjour wolf,
The software Sage L100 accesses the database directly. So, a lock exclusively locks the tables.
To share access to the database, you need to use a client/server part called "Server NT Sage". It works with NT, 2K, 2K3. Be careful with Vista. The first part (and the second one) are installed on the server, the second part is on each user workstation. The protocol used to communicate is TCP/IP.
The ODBC driver accesses through C/S mode, and you can share the database with the Sage application (on the same workstation or another one). Of course, you need to have a multiuser license for the "commercial management". You can obtain the Sage C/S from a Sage reseller. The cost is about 300 €.
----------
Le logiciel Sage L100 accède à la base de données directement. Un verrou bloque les tables de façon exclusive.
Pour partager l'accès à la base de données, tu dois utiliser un module "client serveur" nommé "Serveur Sage pour NT". Ça fonctionne également avec NT, 2000, 2003. Attention avec Vista. La première partie (et la seconde) sont installées sur le serveur, la seconde sur chaque poste utilisateur. Le protocole utilisé est TCP/IP.
Le driver ODBC accède en C/S et tu peux partager ta base de données avec l'appli Sage (sur le même poste ou sur un autre). Bien sûr, tu dois disposer d'une licence multiutilisateurs pour ta gestion commerciale. Tu peux obtenir ce Serveur Sage auprès d'un revendeur au prix d'environ 300 €.
You can email me if you need any further information.
Tu peux m'envoyer un mail si tu as besoin d'autres informations.
Good luck, wolf,
bonne chance,
Phil
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
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
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!!!
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!!!
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
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
Hello,
I have read the various responses you give on this forum and I think that you could save me a lot of time in a very short time.
I personally have a few minor issues with adding in SAGE accounting and gescom.
If you are still in the game, I would like to be able to contact you by phone.
Sincerely.
I have read the various responses you give on this forum and I think that you could save me a lot of time in a very short time.
I personally have a few minor issues with adding in SAGE accounting and gescom.
If you are still in the game, I would like to be able to contact you by phone.
Sincerely.
Good evening Georges,
Give me a "private mail" address on this site and outside the forum. Give me some explanations.
In return, I will send you a way to contact me.
See you later
Phil
--
Wisdom is not a gift. It is learned, patiently, by listening to those around us and by reflecting on things afterwards. Let us therefore become Wise.
Give me a "private mail" address on this site and outside the forum. Give me some explanations.
In return, I will send you a way to contact me.
See you later
Phil
--
Wisdom is not a gift. It is learned, patiently, by listening to those around us and by reflecting on things afterwards. Let us therefore become Wise.
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
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
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
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
- 1
- 2
Next