run-time error 3027 Cannot update

[loisk]

(Access 2000/ODBC to MySQL tables)

Hi,

I am getting the run time erro 3027 and it says that "cannot update: database or object is read only." Does anyone see any errors in my code below? Thanks for the help!

My code is

      outfilename = "ls." & field1 & value1a & "_" & value1b & ".txt"
      MsgBox "outfilename is " & outfilename
      strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase].outfilename FROM myTable;"
 
         DoCmd.RunSQL strSQL

[Stewart Ross]

(Access 2000/ODBC to MySQL tables)

Hi,

I am getting the run time erro 3027 and it says that "cannot update: database or object is read only." Does anyone see any errors in my code below? Thanks for the help!

My code is

 outfilename = "ls." & field1 & value1a & "_" & value1b & ".txt"
MsgBox "outfilename is " & outfilename
strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase].outfilename FROM myTable;"
 
DoCmd.RunSQL strSQL

Hi. I tried out a query based on your code (without the other DB) which tried to connect to the back-end DB; I didn't have a test DB set up to replicate what you are trying to do, but since the RunSQL did what it was supposed to do it appears that the VB code is OK. I can't say that your SQL update is, because it depends whether the other DB recognises it as a valid SQL statement to respond to.

As the SQL query got to the stage where it was passed through to the other DB it suggests to me that either 1) there is a problem at the myDatabase end, or 2) the permissions currently set do not allow you to update the other DB, or 3) this type of SELECT ... INTO is not a valid update query for your other DB. I cannot comment on MySQL (not used it myself), and with no data about whether you have used this type of update before all I can say is that Access is doing what it is supposed to do.

-Stewart

[loisk]

Hi. I tried out a query based on your code (without the other DB) which tried to connect to the back-end DB; I didn't have a test DB set up to replicate what you are trying to do, but since the RunSQL did what it was supposed to do it appears that the VB code is OK. I can't say that your SQL update is, because it depends whether the other DB recognises it as a valid SQL statement to respond to.

As the SQL query got to the stage where it was passed through to the other DB it suggests to me that either 1) there is a problem at the myDatabase end, or 2) the permissions currently set do not allow you to update the other DB, or 3) this type of SELECT ... INTO is not a valid update query for your other DB. I cannot comment on MySQL (not used it myself), and with no data about whether you have used this type of update before all I can say is that Access is doing what it is supposed to do.

-Stewart

Hi Stewart!
Thank you so much for your trying out my code.
One thing that I don't understand is that... if the file name is not a variable, then it renders a txt file OK. For instance,

strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase].outfilename.txt FROM myTable;"
 
         DoCmd.RunSQL strSQL

It creates a txt file with filtered data with no problem. So it seems to be that getting filename from the variables are causing the problem. I am under stress a bit since I have to solve this problem within 2, 3 days. Whew... Any idea you can think of? Thanks again!

[Stewart Ross]

... if the file name is not a variable, then it renders a txt file OK.

Hi, and well done for going straight to the heart of the problem - placing the variable name within the string! VB can't do the substitution that way. I didn't see it before, not being familiar with the MySQL SELECT...INTO construct - but it becomes so obvious now you have mentioned it.

Try

strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]." & outfilename & " FROM myTable;"

Cheers

Stewart

[loisk]

Hi, and well done for going straight to the heart of the problem - placing the variable name within the string! VB can't do the substitution that way. I didn't see it before, not being familiar with the MySQL SELECT...INTO construct - but it becomes so obvious now you have mentioned it.

Try

strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]." & outfilename & " FROM myTable;"

Cheers

Stewart

Wow! Thank you, thank you, thank you!
I owe you thousand times thank you, Stewart!
It works greatly!
That little thingeeee caused me such agony for many days.
Thanks again to you and to "thescript.com" !!

[Stewart Ross]

You are very welcome, loisk, and thank you for your kind words. Really, you pointed out where the problem was yourself, so well done for that.

I am very glad indeed that your query is now working as intended.

Regards

Stewart

[loisk]

You are very welcome, loisk, and thank you for your kind words. Really, you pointed out where the problem was yourself, so well done for that.

I am very glad indeed that your query is now working as intended.

Regards

Stewart

I really appreciate your help!

[loisk]

Hi Stewart,

The previously solved problem with your help gave a birth another question.
The variable for my outputfilename is not rendering properly if I use it with IF statement. I am sure that I am not doing something right in building a string statement.

outfilename = "ls." & field1 & value1a & "_" & value1b & ".txt"
      MsgBox "outfilename is " & outfilename
      strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]. " & outfilename & "FROM myTable;"
 
         DoCmd.RunSQL strSQL

It works just fine to create a text file with the file name I want it to be.
Then, I have to construction a conditional statement for the filename, outfilename. When I use the filename variable within the IF statement, it gives out an error. For example,

if (values1a) > "" and (value1b) = "" then
   outfilename =  "ls." & field1 & value1a  & ".txt"
else
    if (values1a) = "" and (value1b) > "" then 
        outfilename = "ls." & field1 & value1b & ".txt"
end if
 
      MsgBox "outfilename is " & outfilename
      strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]. " & outfilename & "FROM myTable;"
 
         DoCmd.RunSQL strSQL

The MsgBox is not showing the outfilename at all.
Do you find any fault in the code? Thanks!

[Stewart Ross]

Hi again Loisk. Your condition tests are not quite right, and as a result neither branch of your IF statement sets the outfilename (hence blank name in messagebox). Your IFs are also missing an END IF, correctible by changing the middle ELSE to an ELSEIF

if (Nz(values1a)) <> "" and NZ((value1b)) = "" then
    outfilename = "ls." & field1 & value1a & ".txt"
elseif NZ(values1a) = "" and NZ(value1b) <> "" then 
    outfilename = "ls." & field1 & value1b & ".txt"
end if

The Nz function is used to substitute an empty string if the argument passed to it is null.

Please note that your code does not cover the situation where values1a and value1b are both empty.

Hope this helps.

-Stewart

[loisk]

Hi again Loisk. Your condition tests are not quite right, and as a result neither branch of your IF statement sets the outfilename (hence blank name in messagebox). Your IFs are also missing an END IF, correctible by changing the middle ELSE to an ELSEIF

if (Nz(values1a)) <> "" and NZ((value1b)) = "" then
    outfilename = "ls." & field1 & value1a & ".txt"
elseif NZ(values1a) = "" and NZ(value1b) <> "" then 
    outfilename = "ls." & field1 & value1b & ".txt"
end if

The Nz function is used to substitute an empty string if the argument passed to it is null.

Please note that your code does not cover the situation where values1a and value1b are both empty.

Hope this helps.

-Stewart

Hi Stewart again!

Sorry for the incompleted code which I just typed it while it was written, but I have END IF in my real code. (My server is down for other reason right now, so I didn't copy from the real code.) Anyhow, thanks for your quick reply. I will retry with your code as soon as the server is back, and will let you know. Thanks again!