By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

run-time error 3027 Cannot update

P: 97
(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

Expand|Select|Wrap|Line Numbers
  1.       outfilename = "ls." & field1 & value1a & "_" & value1b & ".txt"
  2.       MsgBox "outfilename is " & outfilename
  3.       strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase].outfilename FROM myTable;"
  4.  
  5.          DoCmd.RunSQL strSQL
Mar 14 '08 #1
Share this Question
Share on Google+
11 Replies


Expert Mod 2.5K+
P: 2,545
(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

Expand|Select|Wrap|Line Numbers
  1.  outfilename = "ls." & field1 & value1a & "_" & value1b & ".txt"
  2. MsgBox "outfilename is " & outfilename
  3. strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase].outfilename FROM myTable;"
  4.  
  5. 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
Mar 15 '08 #2

P: 97
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,

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase].outfilename.txt FROM myTable;"
  2.  
  3.          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!
Mar 17 '08 #3

Expert Mod 2.5K+
P: 2,545
... 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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]." & outfilename & " FROM myTable;"
Cheers

Stewart
Mar 17 '08 #4

P: 97
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
Expand|Select|Wrap|Line Numbers
  1. 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"!!
Mar 17 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Mar 17 '08 #6

P: 97
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!
Mar 17 '08 #7

P: 97
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.

Expand|Select|Wrap|Line Numbers
  1. outfilename = "ls." & field1 & value1a & "_" & value1b & ".txt"
  2.       MsgBox "outfilename is " & outfilename
  3.       strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]. " & outfilename & "FROM myTable;"
  4.  
  5.          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,

Expand|Select|Wrap|Line Numbers
  1. if (values1a) > "" and (value1b) = "" then
  2.    outfilename =  "ls." & field1 & value1a  & ".txt"
  3. else
  4.     if (values1a) = "" and (value1b) > "" then 
  5.         outfilename = "ls." & field1 & value1b & ".txt"
  6. end if
  7.  
  8.       MsgBox "outfilename is " & outfilename
  9.       strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\AccessData\\myDatabase]. " & outfilename & "FROM myTable;"
  10.  
  11.          DoCmd.RunSQL strSQL
The MsgBox is not showing the outfilename at all.
Do you find any fault in the code? Thanks!
Mar 26 '08 #8

Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. if (Nz(values1a)) <> "" and NZ((value1b)) = "" then
  2.     outfilename = "ls." & field1 & value1a & ".txt"
  3. elseif NZ(values1a) = "" and NZ(value1b) <> "" then 
  4.     outfilename = "ls." & field1 & value1b & ".txt"
  5. 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
Mar 26 '08 #9

P: 97
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
Expand|Select|Wrap|Line Numbers
  1. if (Nz(values1a)) <> "" and NZ((value1b)) = "" then
  2.     outfilename = "ls." & field1 & value1a & ".txt"
  3. elseif NZ(values1a) = "" and NZ(value1b) <> "" then 
  4.     outfilename = "ls." & field1 & value1b & ".txt"
  5. 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!
Mar 26 '08 #10

P: 97
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!
Hi Stewart,

I am back after our server is recovered! (what a bomb!)

I re-coded as you suggested as followings, but I keep getting an error (3067: Query must contain at least one table or query). Here's my code:

Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(field1) And IsNull(field2) Then                    'if field 1 only
  2.        MsgBox "only field 1 entered"
  3.  
  4.        If Nz(value1a) <> "" And Nz(value1b) = "" Then     '(1)
  5.           MsgBox "Value1a is: " & value1a
  6.              outfilename = "out." & field1 & value1a & ".txt"
  7.              MsgBox "outfilename is " & outfilename
  8.        ElseIf Nz(value1a) <> "" And Nz(value1b) <> "" Then
  9.           MsgBox "Value1b is: " & value1b
  10.           outfilename = "out." & field1 & value1a & "-" & value1b & ".txt"   'value1b & field2 & value2a & value2b &
  11.           MsgBox "outfilename is " & outfilename
  12.  
  13.        End If
  14.  
  15.    ElseIf Not IsNull(field1) And Not IsNull(field2) Then         'if both fields
  16.           MsgBox "field 1 and field 2"
  17.        If Nz(value1a) <> "" And Nz(value1b) = "" And _
  18.           Nz(values2a) <> "" And Nz(value2b) = "" Then     '(3)
  19.            MsgBox "value1a is: " & value1a & "value2a" & value2a
  20.            outfilename = "out." & field1 & value1a & "." & field2 & value2a & ".txt"
  21.            MsgBox "outfilename is " & outfilename
  22.  
  23.        ElseIf Nz(value1a) <> "" And Nz(value1b) <> "" And _
  24.            Nz(value2a) <> "" And Nz(value2b) = "" Then    '(4)
  25.            MsgBox "value1a: " & value1a & "value1b: " & value1b & "value2a: " & value2a
  26.           outfilename = "out." & field1 & value1a & "-" & value1b & "." _
  27.                                      & field2 & value2a & ".txt"
  28.           MsgBox "outfilename is " & outfilename
  29.  
  30.        ElseIf Nz(values1a) <> "" And Nz(value1b) = "" And _
  31.           Nz(values2a) <> "" And Nz(value2b) <> "" Then    '(5)
  32.              MsgBox (value1a) & (value2b)
  33.              outfilename = "out." & field1 & value1a & "." _
  34.                                        & field2 & value2a & "-" & value2b & ".txt"
  35.              MsgBox "outfilename is " & outfilename
  36.  
  37.        ElseIf Nz(values1a) <> "" And Nz(value1b) <> "" And _
  38.              Nz(values2a) <> "" And Nz(value2b) <> "" Then    '(6)
  39.                MsgBox (value1a) & (value2b)
  40.                outfilename = "out." & field1 & value1a & "-" & value1b & "." _
  41.                                        & field2 & value2a & "-" & value2b & ".txt"
  42.                MsgBox "outfilename is " & outfilename
  43.        End If
  44.  
  45.     End If
  46.  
  47.  
  48.  strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
  49.                 outfilename & " FROM queryoutput;"
  50.          Debug.Print strSQL
  51.          DoCmd.RunSQL strSQL
*****************************************
All the msgbox is running fine and it creates the filename fine, except the query.
Apr 14 '08 #11

P: 97
Hi Stewart,

I am back after our server is recovered! (what a bomb!)

I re-coded as you suggested as followings, but I keep getting an error (3067: Query must contain at least one table or query). Here's my code:

Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(field1) And IsNull(field2) Then                    'if field 1 only
  2.        MsgBox "only field 1 entered"
  3.  
  4.        If Nz(value1a) <> "" And Nz(value1b) = "" Then     '(1)
  5.           MsgBox "Value1a is: " & value1a
  6.              outfilename = "out." & field1 & value1a & ".txt"
  7.              MsgBox "outfilename is " & outfilename
  8.        ElseIf Nz(value1a) <> "" And Nz(value1b) <> "" Then
  9.           MsgBox "Value1b is: " & value1b
  10.           outfilename = "out." & field1 & value1a & "-" & value1b & ".txt"   'value1b & field2 & value2a & value2b &
  11.           MsgBox "outfilename is " & outfilename
  12.  
  13.        End If
  14.  
  15.    ElseIf Not IsNull(field1) And Not IsNull(field2) Then         'if both fields
  16.           MsgBox "field 1 and field 2"
  17.        If Nz(value1a) <> "" And Nz(value1b) = "" And _
  18.           Nz(values2a) <> "" And Nz(value2b) = "" Then     '(3)
  19.            MsgBox "value1a is: " & value1a & "value2a" & value2a
  20.            outfilename = "out." & field1 & value1a & "." & field2 & value2a & ".txt"
  21.            MsgBox "outfilename is " & outfilename
  22.  
  23.        ElseIf Nz(value1a) <> "" And Nz(value1b) <> "" And _
  24.            Nz(value2a) <> "" And Nz(value2b) = "" Then    '(4)
  25.            MsgBox "value1a: " & value1a & "value1b: " & value1b & "value2a: " & value2a
  26.           outfilename = "out." & field1 & value1a & "-" & value1b & "." _
  27.                                      & field2 & value2a & ".txt"
  28.           MsgBox "outfilename is " & outfilename
  29.  
  30.        ElseIf Nz(values1a) <> "" And Nz(value1b) = "" And _
  31.           Nz(values2a) <> "" And Nz(value2b) <> "" Then    '(5)
  32.              MsgBox (value1a) & (value2b)
  33.              outfilename = "out." & field1 & value1a & "." _
  34.                                        & field2 & value2a & "-" & value2b & ".txt"
  35.              MsgBox "outfilename is " & outfilename
  36.  
  37.        ElseIf Nz(values1a) <> "" And Nz(value1b) <> "" And _
  38.              Nz(values2a) <> "" And Nz(value2b) <> "" Then    '(6)
  39.                MsgBox (value1a) & (value2b)
  40.                outfilename = "out." & field1 & value1a & "-" & value1b & "." _
  41.                                        & field2 & value2a & "-" & value2b & ".txt"
  42.                MsgBox "outfilename is " & outfilename
  43.        End If
  44.  
  45.     End If
  46.  
  47.  
  48.  strSQL = "SELECT * INTO [Text;HDR=YES;DATABASE=D:\\Access_data\\miha2008]." & _
  49.                 outfilename & " FROM queryoutput;"
  50.          Debug.Print strSQL
  51.          DoCmd.RunSQL strSQL
*****************************************
All the msgbox is running fine and it creates the filename fine, except the query.
My apology! There were a couple of variable name misspelled! It works fine after fixing those. Thanks!
Apr 14 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.