Hi,
First of all, here are my work tools : I have an Access 2003 app that uses an SQL Server DataBase (i know it's weird). I am using Microsoft SQL Server Management Studio Express.
I am now trying to execute a query from my app to insert some rows from 2 tables into another. My query works well when i use it rigth into SQL Server Management or when i create a new query in Access, but it don't work in my app... It gives me the "ODBC Call failed" error for an unkwown reason that i can't discover because my query works well in other situations...
Let's see my VBA code : -
Private Sub cmdReport_Click()
-
On Error GoTo Erreur
-
ErrRoll = False
-
-
'** sauvegarde l'enregistrement !!
-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
-
Dim Db As Database
-
Dim Ws As Workspace
-
Dim Rs As Recordset
-
Dim Rs1 As Recordset
-
Dim Rs2 As Recordset
-
Set Ws = DBEngine.Workspaces(0)
-
Set Db = CurrentDb
-
Dim HasDuplicate As Boolean
-
HasDuplicate = False
-
'** Recherche les doublons dans Tag #
-
Set Rs = Db.OpenRecordset("SELECT DISTINCTROW First(TbTrxInHstDtl.NoTrx) AS NoTrxChamps, First(TbTrxInHstDtl.TagNo) AS UnitéNumberChamps, Count(TbTrxInHstDtl.NoTrx) AS NombreDeDbls FROM TbTrxInHstDtl " _
-
& "GROUP BY TbTrxInHstDtl.NoTrx, TbTrxInHstDtl.tagNo " _
-
& "HAVING (((Count(TbTrxInHstDtl.NoTrx))>1) AND ((TbTrxInHstDtl.NoTrx)=" & Me![NoTrx] & ") AND ((Count(TbTrxInHstDtl.TagNo))>1));", 8)
-
If Not Rs.BOF Then
-
MsgStop ("Le même numéro de Tag est inscrit en double !" & Chr$(13) & "Corrigez.")
-
[RqTrxInDtl].SetFocus
-
Exit Sub
-
End If
-
'*** check si en inventaire on a meme Tag No pour meme BOL(in)
-
Set Rs = Db.OpenRecordset("SELECT TbInventaire.TagNumber, TbTrxInHstHdr.NoTrx FROM (TbInventaire INNER JOIN TbTrxInHstDtl ON TbInventaire.TagNumber = TbTrxInHstDtl.TagNo) INNER JOIN TbTrxInHstHdr ON (TbTrxInHstHdr.NoTrx = TbTrxInHstDtl.NoTrx) AND (TbInventaire.ClientProduitNo = TbTrxInHstHdr.ClientProduitNo) AND (TbInventaire.CliSource = TbTrxInHstHdr.ClientSource) AND (TbInventaire.BolIn = TbTrxInHstHdr.ClientBOLNo) " _
-
& "WHERE (((TbTrxInHstHdr.NoTrx)=" & Me![NoTrx] & "));", 8)
-
If Not Rs.BOF Then
-
If vbNo = MsgConfirmA("Le numéro d'entreposage(U.E.) " & Rs![TagNumber] & " est inscrit" _
-
& Chr$(13) & "en inventaire pour le même client" _
-
& Chr(13) & " et le même numéro d'expédition du client(Exp.#-Client) !" _
-
& Chr(13) & "Voulez-vous reporter quand même ?") Then
-
[RqTrxInDtl].SetFocus
-
Exit Sub
-
End If
-
End If
-
'*** version 3.1 le 06/ déc. 2002
-
'*** y a t il déjà eu une Trx In pour ce client,, meme BOL number
-
'** et meme produit ???
-
'Set Rs1 = Db.OpenRecordset("SELECT TbTrxInHstHdr.ClientSource, TbTrxInHstHdr.ClientProduitNo, TbTrxInHstHdr.ClientBOLNo FROM TbTrxInHstHdr " _
-
' & "WHERE (((TbTrxInHstHdr.ClientSource)='" &[ListClientSource] & "') AND ((TbTrxInHstHdr.ClientProduitNo)='" &[ListClientProduitNo] & "') AND ((TbTrxInHstHdr.ClientBOLNo)=" & [ClientBOLNo] & "));", 8)
-
-
If vbNo = MsgConfirmQ("Reporter cette transaction maintenant ?") Then
-
Exit Sub
-
End If
-
DoCmd.Hourglass True
-
Ws.BeginTrans
-
ErrRoll = True
-
-
'** insère Dtl
-
Db.Execute ("INSERT INTO TbTrxInHstDtl ( NoTrx, TagNo, FormatExp, UnitéDeMesure, Localisation, [Note], Qté, QtéExtension, LigneNo, QtéUnit ) " _
-
& "SELECT TbTrxInHstDtl.NoTrx, TbTrxInHstDtl.TagNo, TbTrxInHstDtl.FormatExp, TbProduits.UnitéDeMesure, TbTrxInHstDtl.Localisation, TbTrxInHstDtl.Note, TbTrxInHstDtl.Qté, TbTrxInHstDtl.QtéExtension, TbTrxInHstDtl.LigneNo, TbTrxInHstDtl.QtéUnit FROM (TbTrxInHstHdr INNER JOIN TbTrxInHstDtl ON TbTrxInHstHdr.NoTrx = TbTrxInHstDtl.NoTrx) INNER JOIN TbProduits ON (TbTrxInHstHdr.ClientSource = TbProduits.CliId) AND (TbTrxInHstHdr.ClientProduitNo = TbProduits.CliProduitNo) " _
-
& "WHERE (((TbTrxInHstDtl.NoTrx)=" & Me![NoTrx] & "));"), dbFailOnError
-
'*** Envoie en inventaire
-
Db.Execute ("INSERT INTO TbInventaire (DateIn, CliSource, ClientProduitNo, CliLotNo, NbreUnits, QtéStock, TotalStock, FormatExp, TagNumber, Localisation, TrxLigneNo, RemorqueIn, BOlIn, TrxNumber, LaNote) " _
-
& "SELECT DateTrx, ClientSource, ClientProduitNo, ClientLotNo, QtéUnit, Qté, QtéExtension, FormatExp, TagNo, Localisation, LigneNo, RemorqueNo, ClientBOLNo, TbTrxInHstHdr.NoTrx, Note " _
-
& "FROM TbTrxInHstHdr INNER JOIN TbTrxInHstDtl ON TbTrxInHstDtl.NoTrx = TbTrxInHstHdr.NoTrx " _
-
& "WHERE TbTrxInHstHdr.NoTrx = " & Me![NoTrx]), dbFailOnError
-
My code is bugging at the last Db.Execute.
Can someone help me please?
3 3573
Guess the INSERT has an error.
Best to place a breakpoint in the code and when the db.execute has been reached to do a print of the string in the immediate window.
Next copy / paste the string into a query and execute it in the query editor to get the exact error message.
Nic;o)
Finaly i don't have this bug anymore... don't know what i did to correct it but it's ok now with this same query.
I definitly hate Access
It might fail when fields are empty, just add some code to test them before executing the query.
Success and "au revoir" :-)
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: tina |
last post by:
Hello,
Can you hale me to define my mistake please?
I am trying to run a SQL Pass – Through Query from Access 2000 and
inside the record set I am trying to loop for a LoginID. I think
that this...
|
by: Yannick Turgeon |
last post by:
Hello all,
I'm using:
- A97 (front-end)
- MySQL drivers 3.51
- MySQL 4.0.18 (back-end)
I've got a MySQL table connected to an Access DB throw an ODBC link. When I
connect to this table, no...
|
by: VBSponge |
last post by:
Hi all. Need help here as I cant see whats wrong...
I have a querydef in Access 2k which execs a stored proc in a SQL
Server 2k db.
I keep getting the following error, and cant stop it for the...
|
by: mcbill20 |
last post by:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to...
|
by: Jimbo |
last post by:
I have a form in access 97 that is populated by a query against some
sql server
tables....when ever the query pulls up only one record im fine..however
if it
pulls up more than one record i have...
|
by: TheKval |
last post by:
I'm trying to pull records on a Macola table linked to Access. If
enter a value in the criteria field of the query form, the quer
executes okay. If I change the criteria to a parameter, ala, , I...
|
by: Harmony407 |
last post by:
Hello:
Our organization uses MS Access to store all of our members
information.
Our primary database computer is running on Windows 98. This is where
the main database exists. The MS Access...
|
by: mukeshhtrivedi |
last post by:
We have 4 MS Access Databases with few tables on each DB. There are
some reports which uses ODBC. When I try to use the report and input
date range and click ok I get error "ODBC --call failed" it...
|
by: alegria4ever |
last post by:
I have an Access 2000 database that links several tables from Oracle 9.
For some reason or another, one of our user repeatedly gets the
following error when accessing queries within this database:...
|
by: Kirby1 |
last post by:
ODBC call failed
--------------------------------------------------------------------------------
We have recently upgraded our SQL Server from 7.0 to 2000. Since then my Access database to...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |