run time error 3207 can not update. Database or object is read only (1 Viewer)

[Summer123]

Hello, this use to work perfectly until i went from 2007 to 2003... basically few of my users need to use this DB in 2003 but they were getting an error, when i did some research i fixed few things but now i am stuck here.. can someone please assist?

error - run time error 3207 can not update. Database or object is read only

Public Sub Standard(zAction As String, Optional zLongFileName As String, Optional zShortFileName As String, Optional zClient As String)
Dim strSQL As String
Dim hCarrier As String, hDate As String
Dim Elapsed As Variant, zTimer As Long, eMin As String, eSec As String
Dim zImport As String
Dim zCurrFolder As String
Dim X As Long
Dim zImportDT As Date
Dim zRecsAll As Long, zRecsPart As Long, zRecsDep As Long
Dim headerRecordFile As String, zero1RecordFile As String, zero2d_RecordFile As String, zero2e_RecordFile As String
zTimer = Int(Timer())
'Retrieve path of current database (includes trailing "\")
    zCurrFolder = myWorkingFolder()
'Branch for action - import or export
    Select Case zAction
        'Case "Export"
            'GoTo Export
        Case "Import"
            'Nothing here
    End Select
 
'------------------------------------------------------------------------------
' Import
'------------------------------------------------------------------------------
zImport = "C:\Documents and Settings\Desktop"
'header records
    DoCmd.DeleteObject acTable, "headerRecord"
 
'(Header_Record)
'(THR_Record)
    Status ("Importing " & zShortFileName & " (HeaderRecord) ...")
[COLOR=red]   DoCmd.TransferText acImportFixed, "headerRecord", "headerRecord", zImport, False, "" - > this is where it boms.. fromwhy i can tell everything looks good and my file is in .txt formt and not in any other format ...i am going crazy here...can anyone hel??? :mad:[/COLOR]
        strSQL = "DELETE * FROM headerRecord where RECORD_IDENTIFIER<>'THR' or RECORD_IDENTIFIER is null;"
        DoCmd.RunSQL strSQL
 
....

thanks! Summer

 

[vbaInet]

zImport???

 

[Summer123]

just giving a name so i dont have to keep writing in docmd, i would be using many of the docmd as the file will be imported many times in different tables.. does that help?

 

[vbaInet]

What I'm saying is that before you run the code the zImport string is:

zImport = "C:\Documents and Settings\Desktop"

Desktop is not a file name. Unless you are going to concatenate the filename to it?

 

[Summer123]

right but i am basically asking it to go there and get a file no?

 

[Summer123]

now i am getting a different error after i adjusted the code a little it bombs at the line below stating "Run time error '75' Path/File access error"

Dim strSQL As String
Dim hCarrier As String, hDate As String
Dim Elapsed As Variant, zTimer As Long, eMin As String, eSec As String
Dim zImport As String
Dim zCurrFolder As String
Dim X As Long
Dim zImportDT As Date
Dim zRecsAll As Long, zRecsPart As Long, zRecsDep As Long
Dim headerRecordFile As String, zero1RecordFile As String, zero2d_RecordFile As String, zero2e_RecordFile As String
zTimer = Int(Timer())
'Retrieve path of current database (includes trailing "\")
    zCurrFolder = myWorkingFolder()
'Branch for action - import or export
    Select Case zAction
        'Case "Export"
            'GoTo Export
        Case "Import"
            'Nothing here
    End Select
 
'------------------------------------------------------------------------------
' Import
'------------------------------------------------------------------------------
'Copy source file to database folder on C: drive for faster multiple imports
'Only if selected file is not already on C: drive
    If Left(zLongFileName, 1) <> "C" Then
        Status ("Copying " & zShortFileName & " to folder " & zCurrFolder & "...")
        FileCopy zLongFileName, zCurrFolder & zShortFileName [COLOR=red][B]-> here is where it bombs
[/B][/COLOR]        zImport = zCurrFolder & zShortFileName
    Else
        zImport = zLongFileName
    End If
'Rename file to "import.txt" to avoid double-period filename import problems
'Will be renamed to original filename after import
    On Error Resume Next
    Kill zCurrFolder & "import.txt"
    On Error GoTo 0
    
    Name zImport As zCurrFolder & "import.txt"
    zImport = zCurrFolder & "import.txt"
    zImportDT = FileDateTime(zImport)
 
'delete header records
    DoCmd.DeleteObject acTable, "headerRecord"
 
'(Header_Record)
'(THR_Record)
    Status ("Importing " & zShortFileName & " (HeaderRecord) ...")
   DoCmd.TransferText acImportFixed, "headerRecord", "headerRecord", zImport, False, ""
        strSQL = "DELETE * FROM headerRecord where RECORD_IDENTIFIER<>'THR' or RECORD_IDENTIFIER is null;"
        DoCmd.RunSQL strSQL
 
....

 

[boblarson]

I don't see anywhere that you have assigned a value to
zShortFileName

Where are you setting that value? What IS the value being set and do you have the file extension included?

 

[nanscombe]

From the original post, it appears to be a parameter passed into the sub.

Public Sub Standard(zAction As String, Optional zLongFileName As String, Optional zShortFileName As String, Optional zClient As String)

I wonder if it fails because the target file already exists?

You could always try deleting the target file before the copy.

Status ("Copying " & zShortFileName & " to folder " & zCurrFolder & "...")
[B]Kill zCurrFolder & zShortFileName[/B]
FileCopy zLongFileName, zCurrFolder & zShortFileName -> here is where it bombs

But you would need to trap, or ignore, the error if the file didn't already exist.

 

[vbaInet]

It would be useful to know what the exact error message is too.

 

[boblarson]

From the original post, it appears to be a parameter passed into the sub.

But is the parameter passed correctly, using the file extension too? That is a common mistake I've found people make - forgetting to include the file extension.

I would also check to see if creating a concatenated variable and using it would work. I've seen a problem with trying to concatenate directly in the filecopy code.

So

Dim strNewFile As String
 
Status ("Copying " & zShortFileName & " to folder " & zCurrFolder & "...")
strNewFile = zCurrFolder & zShortFileName
[B]Kill strNewFile[/B]
 
FileCopy zLongFileName, [B]strNewFile[/B]

And also does the zCurrFolder have the backslash, which if it doesn't then it would also need to be added.

But you would need to trap, or ignore, the error if the file didn't already exist.[/QUOTE]

 

[nanscombe]

But is the parameter passed correctly, using the file extension too? That is a common mistake I've found people make - forgetting to include the file extension.

I would also check to see if creating a concatenated variable and using it would work. I've seen a problem with trying to concatenate directly in the filecopy code.

Yes, that could well be a problem depending on how you get your filenames.

I tend to concatenate the source & target filenames before doing any file copying or moving. It makes it easier to make directories, and kill any existing files, before the actual file action.

And also does the zCurrFolder have the backslash, which if it doesn't then it would also need to be added.

Indeed, I always include a line to make sure, something like:

If right$(Directory,1) <> "\" then Directory = Directory & "\"