ODBC -call failed, True/False (1 Viewer)

H

Hello1

Registered User.
Local time
Today, 17:00
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: 43
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 07:00
Joined
Oct 29, 2018
Messages
21,447
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.
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 07:00
Joined
Oct 29, 2018
Messages
21,447
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...
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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:
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 07:00
Joined
Oct 29, 2018
Messages
21,447
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.
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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))
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:00
Joined
Aug 30, 2003
Messages
36,131
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.
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:00
Joined
Aug 30, 2003
Messages
36,131
It could be confused with the table and field names being the same. Try

WHERE Table1.[Table1] <> 0 AND...
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:00
Joined
Aug 30, 2003
Messages
36,131
You don't have a With block, so you'd need the recordset variable before the last 3 lines.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:00
Joined
Aug 30, 2003
Messages
36,131
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?
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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.
 
H

Hello1

Registered User.
Local time
Today, 17:00
Joined
May 17, 2015
Messages
271
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

C
Showing a checkbox in a listbox or combo box
Replies
14
Views
601
CJ_London
C
S
Hi. I am new to this group and I am not a profesional. I am 66 years old and a retired Bank Officer active with Retired Bank Officers community.
Replies
8
Views
291
Suresh1957
S
N
Solved Scraping Json from a web using VBA
Replies
11
Views
463
isladogs
isladogs
N
Solved How to avoid the domain functions in Ms Access
Replies
13
Views
550
nector
N
J_Fo
Solved Add record if not found, still adding record
Replies
6
Views
355
J_Fo
J_Fo

Users who are viewing this thread

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