Hi;



I'm trying to do a (I think) very simple thing.  I want to test the following function.  I want to read a 

fixed width field text file into an existing table.  The code follows:



Option Compare Database

Option Explicit



Public Function ImportText()

   DoCmd.SetWarnings False

   DoCmd.RunSQL "DELETE [tRawData].* FROM [tRawData];"

   DoCmd.TransferText acImportFixed, "rawdata", "tRawData", "c:\scans\2001\s02670.dat"

   DoCmd.SetWarnings True

End Function





The first line (RunSQL) works, i.e., the table tRawData is 'cleared' but I get the following error message 

for the second (TransferText) command:



Run-time error '3027':

Cannot update.  Database or object is read only.





When I click 'DeBug' the entire line DoCmd.TransferText acImportFixed, "rawdata", "tRawData", 

"c:\scans\2001\s02670.dat" is highlighted.



Any suggestions would be greatly appreciated.

Thanks,

Joe

Here is my suggestion.  Instead of RunSQL statement, I would create a query

that clears the table.  Then, call the query in your ImportText function



DoCmd.OpenQuery "dqryAllRecordsFromtRawData"



I'm not sure if that will work, but it least it will rule out possibility of

the RunSQL statement keeping the table read only for some reason.



Does the function work if you comment out the RunSQL command?



John



-----Original Message-----

From: jviscomi@s... [mailto:jviscomi@s...]

Sent: Thursday, February 21, 2002 10:21 PM

To: Access

Subject: [access] simple function problem in access





Hi;



I'm trying to do a (I think) very simple thing.  I want to test the

following function.  I want to read a

fixed width field text file into an existing table.  The code follows:



Option Compare Database

Option Explicit



Public Function ImportText()

   DoCmd.SetWarnings False

   DoCmd.RunSQL "DELETE [tRawData].* FROM [tRawData];"

   DoCmd.TransferText acImportFixed, "rawdata", "tRawData",

"c:\scans\2001\s02670.dat"

   DoCmd.SetWarnings True

End Function





The first line (RunSQL) works, i.e., the table tRawData is 'cleared' but I

get the following error message

for the second (TransferText) command:



Run-time error '3027':

Cannot update.  Database or object is read only.





When I click 'DeBug' the entire line DoCmd.TransferText acImportFixed,

"rawdata", "tRawData",

"c:\scans\2001\s02670.dat" is highlighted.



Any suggestions would be greatly appreciated.

Thanks,

Joe

Try putting DoEvents statement between DoCmd.RunSQL "DELETE [tRawData].* 

FROM [tRawData];" and DoCmd.TransferText acImportFixed, "rawdata", 

"tRawData", "c:\scans\2001\s02670.dat" statements.



for example,

DoCmd.RunSQL "DELETE [tRawData].* FROM [tRawData];" 

DoEvents

DoCmd.TransferText acImportFixed, "rawdata", "tRawData", "c:\scans\2001\s02

670.dat"



It could be the fact that TransferText statement attempts to start befre 

RunSQL statement has finished.



Just a guess...



____________________________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10, WALLSEND NSW 2287

Phone: (02) 4924 6336 Fax: (02) 4924 6209

www.hcha.org.au

____________________________________________________



The doors we open and close each day decide the lives we live

____________________________________________________



CONFIDENTIALITY & PRIVILEGE NOTICE

The information contained in this email message is intended for the named 

addressee only.  If you are not the intended recipient you must not copy, 

distribute, take any action reliant on, or disclose any details of the 

information in this email to any other person or organisation.  If you 

have received this email in error please notify us immediately.



>>> jviscomi@s... 22/02/2002 9:21:16 >>>

Hi;



I'm trying to do a (I think) very simple thing.  I want to test the 

following function.  I want to read a

fixed width field text file into an existing table.  The code follows:



Option Compare Database

Option Explicit



Public Function ImportText()

   DoCmd.SetWarnings False

   DoCmd.RunSQL "DELETE [tRawData].* FROM [tRawData];"

   DoCmd.TransferText acImportFixed, "rawdata", "tRawData", "c:\scans\2001\

s02670.dat"

   DoCmd.SetWarnings True

End Function





The first line (RunSQL) works, i.e., the table tRawData is 'cleared' but I 

get the following error message

for the second (TransferText) command:



Run-time error '3027':

Cannot update.  Database or object is read only.





When I click 'DeBug' the entire line DoCmd.TransferText acImportFixed, 

"rawdata", "tRawData",

"c:\scans\2001\s02670.dat" is highlighted.



Any suggestions would be greatly appreciated.

Thanks,

Joe








This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient,
please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily
the views of Hunter Health.

Hi John;



Thanks for your quick response.  Actually, I was able to solve the problem late last night, turns 

out it was a system type problem.  For some reason; the code will not work because of the 

".dat" extension.  

This is very odd and I'll tell you why.  I'd be interested to hear your thoughts.



This application is part (the key part) of a larger application someone created for us some 

years ago in access 97.  Several of us use it, however, I least of all.  Now in our offices 

machines are updated and, according to need, some machines are passed down.  Fortunately, 

as a statistician, I always get the newest machines while my older one is 'passed down', e.g., 

to clerical staff.



Here's the old thing;  one day I went to use the application and I get nothing, I get the same 

error I mentioned yesterday.  Since it was not essential that I run the application the problem 

was ignored.  Over time I have learned enough of VB to try to create the application in 

access2000.  Guess what?  Same Error.



The MS solution (if you're still interested) can be found at:



http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304206



I choose the programming solution and I can tell you it works fine.



But what I still don't know is why some machines and not others?  Can the registry (for I 

assume this is a registry system problem) shipped with different versions of MS be so 

different?

Strange huh?



Anyways, I appreciate your quick response and interest.  If you feel like it, I'd be interested to 

hear your thoughts.

Thanks,



Kind Regards,

Joe





> Here is my suggestion.  Instead of RunSQL statement, I would create a 

query

> that clears the table.  Then, call the query in your ImportText function

> 

> DoCmd.OpenQuery "dqryAllRecordsFromtRawData"

> 

> I'm not sure if that will work, but it least it will rule out possibility 

of

> the RunSQL statement keeping the table read only for some reason.

> 

> Does the function work if you comment out the RunSQL command?

> 

> John

> 

> -----Original Message-----

> From: jviscomi@s... [mailto:jviscomi@s...]

> Sent: Thursday, February 21, 2002 10:21 PM

> To: Access

> Subject: [access] simple function problem in access

> 

> 

> Hi;

> 

> I'm trying to do a (I think) very simple thing.  I want to test the

> following function.  I want to read a

> fixed width field text file into an existing table.  The code follows:

> 

> Option Compare Database

> Option Explicit

> 

> Public Function ImportText()

>    DoCmd.SetWarnings False

>    DoCmd.RunSQL "DELETE [tRawData].* FROM [tRawData];"

>    DoCmd.TransferText acImportFixed, "rawdata", "tRawData",

> "c:\scans\2001\s02670.dat"

>    DoCmd.SetWarnings True

> End Function

> 

> 

> The first line (RunSQL) works, i.e., the table tRawData is 'cleared' but 

I

> get the following error message

> for the second (TransferText) command:

> 

> Run-time error '3027':

> Cannot update.  Database or object is read only.

> 

> 

> When I click 'DeBug' the entire line DoCmd.TransferText acImportFixed,

> "rawdata", "tRawData",

> "c:\scans\2001\s02670.dat" is highlighted.

> 

> Any suggestions would be greatly appreciated.

> Thanks,

> Joe

> 




> 

I couldn't get in to respond on the original thread so I recreated it her 

(kinda).  I suspect that one of the machines had an Office Service Pack 

update (or Jet update) that included Microsoft's security "lockdown".  I 

read the Q bulletin and this seems similar to what they did to try and 

lockdown Outlook attachments.  Is it possible that one machine (the one 

that gets the error) has an update that the other does not?



R





Hi John;



Thanks for your quick response.  Actually, I was able to solve the 

problem late last night, turns 

out it was a system type problem.  For some reason; the code will not 

work because of the 

".dat" extension.  

This is very odd and I'll tell you why.  I'd be interested to hear your 

thoughts.



This application is part (the key part) of a larger application someone 

created for us some 

years ago in access 97.  Several of us use it, however, I least of all.  

Now in our offices 

machines are updated and, according to need, some machines are passed 

down.  Fortunately, 

as a statistician, I always get the newest machines while my older one 

is 'passed down', e.g., 

to clerical staff.



Here's the old thing;  one day I went to use the application and I get 

nothing, I get the same 

error I mentioned yesterday.  Since it was not essential that I run the 

application the problem 

was ignored.  Over time I have learned enough of VB to try to create the 

application in 

access2000.  Guess what?  Same Error.



The MS solution (if you're still interested) can be found at:



http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304206



I choose the programming solution and I can tell you it works fine.



But what I still don't know is why some machines and not others?  Can the 

registry (for I 

assume this is a registry system problem) shipped with different versions 

of MS be so 

different?

Strange huh?



Anyways, I appreciate your quick response and interest.  If you feel like 

it, I'd be interested to 

hear your thoughts.

Thanks,



Kind Regards,

Joe