lack of errormessage

M

mi

I am running a SQLquestion in VB. This does not work and the
errormessage I get is poor:
"cannot execute a select query"

SQLstr = "SELECT * FROM tblMag1;"
CurrentDb.Execute SQLstr

I know it's possible to get moore information than that but I have
forgotten how.

Can anyone please help me.

Thanks in advance
/[email protected]
 
T

Tony Toews

I am running a SQLquestion in VB. This does not work and the
errormessage I get is poor:
"cannot execute a select query"

SQLstr = "SELECT * FROM tblMag1;"
CurrentDb.Execute SQLstr

add ", dbfailonerrors" to the currentdb line.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
M

mi

SELECT statements cannot be .Executed.


I changed the selction to:
SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1 ; "
CurrentDb.Execute SQLstr,dbfailonerrors

The result is the same as before: 'cannot execute the query'.
Does dbfailonerrors needs a definition?

/Michelle
 
M

mi

add ", dbfailonerrors" to the currentdb line.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/

I try to explanation a little better what I'm doing.

Public Sub importTmpTb()
On Error GoTo Err_importTmpTb

Dim bild As Integer
Dim magasin As String
Dim beskrivning As String

I have tried select

SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1
WHERE ] tbMag1].[bild] = 3 ; "

SQLstr = "SELECT [datum],[bild] FROM tblMag1 WHERE [bild] = 3; "

as well as two differen execute

CurrentDb.Execute SQLstr,dbfailonerrors
CurrentDb.Execute SQLstr


And nothing works.

Can please anyone give me some idea what I'm doing wrong.
Thanks in advance.

/Michelle
 
D

David-W-Fenton

:
innews:ljqo4792gd7lrdnj4rglr31b (e-mail address removed):




SELECT statements cannot be .Executed.

I changed the selction to:
SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1 ;
"
CurrentDb.Execute SQLstr,dbfailonerrors

The result is the same as before: 'cannot execute the query'.
Does dbfailonerrors needs a definition?

Only DML statements (i.e., what are referred to in Access as "action
queries," i.e., queries that MODIFY data) can be EXECUTED.

What are you trying to do with your SELECT statement? Do you want to
open a recordset? Display the data to a user?
 
T

Tony Toews

Can please anyone give me some idea what I'm doing wrong.

As David points out, and I missed, is that you can't run a SELECT
query using an Execute statement because there is no action, such as
update, insert or delete to perform.

We need to know what you want to do with the query to help you
further. You likely want a recordset variable so you can loop
through the recordset and do something.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
M

mi

It makes me really glad that someone wants to help me.

I have excel-files that contain 4 columns which in the end shall
inserts in another table with 12 columns.

I’ve transformed the excel-files to tables.
So now I want to read the new tables and add them to the main table
but depending of the data in the 4 columns I will add different data
in the rest 8 columns.

That is why I’m trying to do select in the table.

Is that clear enough?

/Michelle
 
M

mi

It makes me really glad that someone wants to help me.

I have excel-files that contain 4 columns which in the end shall
inserts in another table with 12 columns.

I’ve transformed the excel-files to tables.
So now I want to read the new tables and add them to the main table
but depending of the data in the 4 columns I will add different data
in the rest 8 columns.

That is why I’m trying to do select in the table.

Is that clear enough?

/Michelle

Noone go un idea how to solve this?

/Michelle
 
T

Tony Toews

It makes me really glad that someone wants to help me.

I have excel-files that contain 4 columns which in the end shall
inserts in another table with 12 columns.

I’ve transformed the excel-files to tables.
So now I want to read the new tables and add them to the main table
but depending of the data in the 4 columns I will add different data
in the rest 8 columns.

Ok, then you either want an append query with logic in it to insert
the appropriate data. Or a recordset with a loop reading through all
the records doing recordset adds.

For sample recordset logic see
http://www.granite.ab.ca/access/email/recordsetloop.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
M

mi

add ", dbfailonerrors" to the currentdb line.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/

I try to  explanation a little better what I'm doing.

Public Sub importTmpTb()
On Error GoTo Err_importTmpTb

Dim bild As Integer
Dim magasin As String
Dim beskrivning As String

I have tried select

SQLstr = "SELECT  [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1
WHERE ] tbMag1].[bild]   = 3 ; "

 SQLstr = "SELECT [datum],[bild] FROM tblMag1 WHERE  [bild]  = 3; "

as well as two differen execute

 CurrentDb.Execute SQLstr,dbfailonerrors
 CurrentDb.Execute SQLstr

And nothing works.

Can please anyone give me some idea what I'm doing wrong.
Thanks in advance.

/Michelle- Dölj citerad text -

- Visa citerad text -

Public Sub importTmpTb()

fI've tried that aswell
Dim Datum As String
Dim bild As String
Dim magasin As String
Dim beskrivning As String


SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild],[tbMag1].
[beskrivning],[tbMag1].[magasin] FROM tblMag1 ; "
CurrentDb.Execute SQLstr, dbfailonerrors


I g've got the message as befor.:
cannot execute a select query

/Michelle
 
T

Tony Toews

SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild],[tbMag1].
[beskrivning],[tbMag1].[magasin] FROM tblMag1 ; "
CurrentDb.Execute SQLstr, dbfailonerrors


I g've got the message as befor.:
cannot execute a select query

That error message is by design. You can't execute a SELECT query.
There is nothing there to execute. No INSERTs, UPDATEs or DELETEs.

What is your objective with the query in the code? What are you
trying to do?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
M

mi

 SQLstr = "SELECT  [tbMag1].[datum],[tbMag1].[bild],[tbMag1].
[beskrivning],[tbMag1].[magasin] FROM tblMag1 ; "
  CurrentDb.Execute SQLstr, dbfailonerrors
I g've got the message as befor.:
cannot execute a select query

That error message is by design.  You can't execute a SELECT query.
There is nothing there to execute.  No INSERTs, UPDATEs or DELETEs.

What is your objective with the query in the code?  What are you
trying to do?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/

I have excel-files that contain 4 columns which in the end shall
inserts in another table, we can call it table X, wich contanes 12
columns.

I’ve transformed the excel-files to tables.
So now I want to read the new tables and add them to the to table X
but depending of the data in the 4 columns I will add different data
in the rest 8 columns, in table X.

That is why I’m trying to do select in the table.

Anyone got an idea how that can be done?

Please can anyone help me?
Thanks in advance.
/Michelle
 
T

Tony Toews

I’ve transformed the excel-files to tables.
So now I want to read the new tables and add them to the to table X
but depending of the data in the 4 columns I will add different data
in the rest 8 columns, in table X.

That is why I’m trying to do select in the table.

Anyone got an idea how that can be done?

Please can anyone help me?

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

Then you can add records to tables by using

dim rsadd as dao.recordset

and inside the loop

rsadd.addnew
rsadd!field1 = "something"
rsadd!field2 = 123
rsadd.update

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top