ODBC -call failed, True/False

H

Hello1

Registered User.
Local time
Today, 17:13
Joined
May 17, 2015
Messages
271
Im trying to move my database to Ms SQL Server 2008. I did that upsizing and checked the tables and everything seems to be fine.
However, when I run a piece of my code I get the error from the title.
This is the line where is appears:
Code: 
Set NewVariable = MyDb.OpenRecordset("SELECT * FROM Table1 WHERE  Table1 AND IdTable1 >" & SomeOtherVariable, dbOpenDynaset, dbSeeChanges)
Table1 field is a true/false (Yes/No) field, IdTable1 is autonumber and SomeOtherVariable contains a number.
In the old application (.accdb where I keep forms and queries) where the linked database is .mdb the code runs fine.
I found out that this might be causing the problem "WHERE Table1", I tried "WHERE Table1 = True", "WHERE Table1 = 1" also "WHERE Table1 <> False" but it didnt help. When I remove that part it works and the code is:
Code: 
Set NewVariable = MyDb.OpenRecordset("SELECT * FROM Table1 WHERE   IdTable1 >" & SomeOtherVariable, dbOpenDynaset, dbSeeChanges)
However, I need that ture fals parametar, I have no idea what could be the problem.
 

Attachments

  • ODBC.png
    ODBC.png
    3.1 KB · Views: 93
Hi. Do you know if the upsizing Wizard creates a DSN for you or not? I am not sure, but if you don't have one, then perhaps either Access is trying to use a default one, that doesn't work, or you don't have one, and Access don't know what to do.
 
Hey DB, I made the DNS manually through ODBC data source administrator.
I wouldnt say anything is wrong with it because all the linked tables open fine and all the other reports/forms work correctly.

Edit: I go quickly link back some tables which are used in that code to the old .mdb database and see what happens
 
Hello1 said:
Hey DB, I made the DNS manually through ODBC data source administrator.
I wouldnt say anything is wrong with it because all the linked tables open fine and all the other reports/forms work correctly.

Edit: I go quickly link back some tables which are used in that code to the old .mdb database and see what happens
Click to expand...
Okay. Let us know how it goes...
 
Alright, I linked just the Table1 back and yeah the code works, thats the table I use in that recordset Variable, "NewVariable".
Does that mean something is wrong with the SQL table? But then again, why does it work when I remove the True/False part :confused:
 
Hello1 said:
Alright, I linked just the Table1 back and yeah the code works, thats the table I use in that recordset Variable, "NewVariable".
Does that mean something is wrong with the SQL table? But then again, why does it work when I remove the True/False part :confused:
Click to expand...
Hi. I don't think SQL Server has a Boolean field data type. You'll have to check what the upsizing wizard used when it converted your Access table into SQL Server. You could then try to adjust your code to use a more appropriate value.
 
Data type: bit, size 1 and 0 as default like everywhere else where I have true/false and the other work in other code.
Actually by setting one of my other recordset variable to nothing just a line before the problematic line executes the code runs but then it gets stuck later at Wrks.CommitTrans saying I didnt even start that workspace
Wrks is (Set Wrks = DBEngine.Workspaces(0))
 
To clarify, "Table1" is the name of a field as well as the name of the table? Seems odd.

FYI, bit is the appropriate data type in SQL Server, but make sure it doesn't allow Null. I've heard of wonky errors if it has Null values.
 
Yup, its the field has the same name as the table. You are right, it was set to allow zeroes and I changed it, but still same happening.
When I make a query in access with the query design tool (same as the one in code) it runs fine, however when I stop the code with a brake point on the NewVariable = .. line and try to run the same query from query design it gives me the same error.

Edit: I will make that query again and then go line by line in the code and see when does it stop to work, if it means anything.
 
It could be confused with the table and field names being the same. Try

WHERE Table1.[Table1] <> 0 AND...
 
Ok, I went line by line and it stops when I open the same table in another recordset variable and add a record to it, immediately after the .Update executes the query doesnt work anymore.
Code: 
Set MyRs1 = MyDb.OpenRecordset("Table1", dbOpenDynaset, dbSeeChanges)
.AddNew                                
   !Table1 = True
.Update
I tried -1 instead true but same thing.
I will try your suggestion but same thing also.
 
You don't have a With block, so you'd need the recordset variable before the last 3 lines.
 
Never mind, if it doesn't stop until the update line you must have a With block defined. Can you add a record in the table just by populating that field? Or are there required fields that aren't being populated?
 
There is a with, I didnt post the whole code, sorry for the confusion.
Its actually a recordset inside a recordset too much to post everything.
Anyway the code works fine, after the .Update it keeps going fine I just can run the query in query design after that moment.
Code: 
Set MyQry = MyDb.QueryDefs("qryJustAQuery")
                MyQry.Parameters("forms!frmOcitavanje!SifraMreze") = [Forms]![frmocitavanje]![SifraMreze]
                MyQry.Parameters("forms!frmOcitavanje!SifraSek") = [Forms]![frmocitavanje]![SifraSek]
Set MyRs = MyQry.OpenRecordset
Set MyRs1 = MyDb.OpenRecordset("Table1", dbOpenDynaset, dbSeeChanges)
    MyRs.MoveFirst
        While Not (MyRs.EOF)
           With MyRs1
               .AddNew                                
                   !Table1 = True
               .Update
            End With
         MyRs.MoveNext
         Wend
 
I didnt see your last post up until the morning. Yes I add a record with just populating that one field, all other are not required except the autonumber which populates itself, but I do populate some other fields too, I just didnt include them in this copy of the code.
 
If I comment out the next code in my code it works fine:

Code: 
Set Wrks = DBEngine.Workspaces(0)
'Wrks.BeginTrans
'Wrks.CommitTrans
So, BeginTrans and CommitTrans.
 
You must log in or register to reply here.

Similar threads

D
Solved Adding Records Using VBA
2
Replies
21
Views
3,283
DDJKSA
D
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
714
Minty
Minty
C
Solved Error 3021: No Current record
Replies
2
Views
435
Consonanza
C
J
ms access query by insert a random record which not exist in other table
Replies
6
Views
1,074
jdlc
J
Bobcat42
Solved Problem filling a textbox (format RichText) with Richtext
Replies
3
Views
637
Bobcat42
Bobcat42

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom