Bad File Mode Access 2003, works fine on other PC

[ToddRhodesECI]

Hey guys. I have the following script:

Private Sub btnExportFile_Click()
'On Error GoTo Err_btnExportFile_Click


Dim Script1 As String
Dim strSQL1 As String
Dim strSQL1Output As String
Dim strSQL2 As String
Dim strSQL2Output As String
Dim strSQL3 As String
Dim strSQL3Output As String
Dim strSQL4 As String
Dim strSQL4Output As String
Dim strSQL5 As String
Dim strSQL5Output As String
Dim strSQL6 As String
Dim strSQL6Output As String
Dim strSQL7 As String
Dim strSQL7Output As String
Dim strSQL8 As String
Dim strSQL8Output As String
Dim strSQL9 As String
Dim strSQL9Output As String
Dim intFile As Integer
Dim SQLOutput As String
Dim db As Database, rs As ADODB.Recordset

Set rs = New ADODB.Recordset
strSQL1 = "Select * from Part1"
rs.Open strSQL1, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rs
While Not .EOF
strSQL1Output = rs!Part1
.MoveNext
Wend
.Close

End With
Set rs = Nothing
MsgBox ("Section 1 Complete...")

Set rs = New ADODB.Recordset
strSQL2 = "Select * from Part2"
rs.Open strSQL2, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rs
While Not .EOF
strSQL2Output = rs!Part2
.MoveNext
Wend
.Close

End With
Set rs = Nothing
MsgBox ("Section 2 Complete...")

Set rs = New ADODB.Recordset
strSQL3 = "Select * from Part3"
rs.Open strSQL3, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rs
While Not .EOF
strSQL3Output = rs!Part3
.MoveNext
Wend
.Close

End With
Set rs = Nothing
MsgBox ("Section 3 Complete...")

Set rs = New ADODB.Recordset
strSQL4 = "Select * from Part4"
rs.Open strSQL4, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rs
While Not .EOF
strSQL4Output = rs!Part4
.MoveNext
Wend
.Close

End With
Set rs = Nothing
MsgBox ("Section 4 Complete...")

If txtDivision = "" Then
txtDivision = Null
End If


Script1 = "insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('ADIServerName','" & txtCompanyCode & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue) Values('ADIDBName'
,'ADI_" & txtCompanyCode & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Pay_Group_ID','" & txtPayGroupID & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue) Values('Shift_ID','"
& txtShiftID & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Accrual_Group_ID','" & txtAccrualGroupID & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Points_Group_ID','" & txtPointsGroupID & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue) Values('Super_ID','"
& txtSuperID & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Auto_Punch','" & txtAutoPunch & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Allow_Web_Entry','" & txtAllowWebEntry & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Paid_Holidays','" & txtPaidHolidays & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue) Values('Loc_ID','" &
txtLocID & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue) Values('Div_Code','"
& txtDivCode & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue) Values('Get_Rate','"
& txtGetRate & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('Card_Override','" & txtCardOverride & "')" & vbCrLf & _
"insert dbo.ADI_Defaults (ADI_Field,DefaultValue)
Values('DownloadRateToADI','" & txtDownloadRateToADI & "')" & vbCrLf &
_
"insert dbo.ADI_Departments (Loc_ID,Div_Code,Dept_Code,Position)
Values('" & txtLocation & "','" & txtDivision & "','" & txtDepartment &
"','" & txtPosition & "')" & vbCrLf & _
"insert dbo.ADI_Filter (EmpowerTable,EmpowerField,FilterValue)
Values('" & txtEmpowerTable & "','" & txtEmpowerField & "','" &
txtFilterValue & "')"

intFile = FreeFile
'Open "C:\" & txtCompanyCode & " - Integration Script.txt" For
Output As intFile
Open "\\eci-dzoflw13xtv\adi\test.txt" For Output As intFile
Print #intFile, strSQL1Output & vbCrLf & vbCrLf & Script1 & vbCrLf
& _
vbCrLf & strSQL2Output & vbCrLf & vbCrLf & strSQL3Output & vbCrLf &
vbCrLf & strSQL4Output

MsgBox (txtCompanyCode & " - Integration Script.txt Created!")
Close intFile

Exit_btnExportFile_Click:
Exit Sub

'Err_btnExportFile_Click:
' MsgBox Err.Description
' Resume Exit_btnExportFile_Click



End Sub



Private Sub btnCloseForm_Click()
On Error GoTo Err_btnCloseForm_Click


DoCmd.Close

Exit_btnCloseForm_Click:
Exit Sub

Err_btnCloseForm_Click:
MsgBox Err.Description
Resume Exit_btnCloseForm_Click

End Sub


If I try to run this on my work laptop (winXP SP2, Office 2003, just
updated today with all the latest updates for troubleshooting
purposes), I get an error: 54, Bad File Mode every time I try to run
it. I have tried writing to C:/, a new share on my machine with "Full
Control" access for Everyone, nothing seems to fix it.

If I run the same script at home (same specs, winXP2, Office 2003 just
recently updated), it works without a problem.

So, the syntax should be correct. I had my LAN administrator disable my
Kaspersky Anti-Virus for troubleshooting, but that didn't fix it. I can
run my laptop as local administrator and it still doesn't work. I
really need this to work but can't get it for the life of me. That it
works at home is great for letting me know that I wrote it correctly,
but it doesn't help me get my work done. If anyone can help out at all,
I'm willing to try anything. Thank you much for your time!


Todd Rhodes
Implementation Analyst, ECI Inc.

 

[Phillip Windell]

I am suspicious of this.....:

Dim intFile As Integer
intFile = FreeFile <<<<----this
Open "\\eci-dzoflw13xtv\adi\test.txt" For Output As intFile <<<<---and
this

........But I am not sure about it enough to suggest anything.

You could simplify the script a lot by just Dim'ing a single "strSQL" and
than dynamically concatinating the numerical digit on the end of "Select *
from Part1".
Like maybe:
---------------------------------------------------
Option Explicit
Dim i as Integer
Dim strSQL as String
Dim strSQLOutput as String

For i = 1 to 4 'Has 4 sections so loops 4 times
strSQL = "Select * from Part " & i
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
While Not .EOF '<<<---Loops the number of records in the Set
strSQLOutput = strSQLOutput & rs!Part '<<<---Adjust for that
somehow
rs.MoveNext
rs.Close
Wend
MsgBox ("Section " & i & " Complete...")
Next
Set rs = Nothing

 

[ToddRhodesECI]

That is a heck of a lot simpler. I don't have that much formal training
in any programming languages so I'm still learning some of the tricks
of the trade.

As for the portion of code you're suspicious of, I have tried
everything there, the standard is just "C:\test.txt" but I wanted to
try a share on my laptop with unlimited permissions, just to
troubleshoot the file system aspects of the problem.

Thank you very much for the suggestion, I will use your idea in the
code. I can't get better without good pointers like yours. Now if I
could just write to the darned file on my laptop .

Todd.

I am suspicious of this.....:

Dim intFile As Integer
intFile = FreeFile <<<<----this
Open "\\eci-dzoflw13xtv\adi\test.txt" For Output As intFile <<<<---and
this

.......But I am not sure about it enough to suggest anything.

You could simplify the script a lot by just Dim'ing a single "strSQL" and
than dynamically concatinating the numerical digit on the end of "Select *
from Part1".
Like maybe:
---------------------------------------------------
Option Explicit
Dim i as Integer
Dim strSQL as String
Dim strSQLOutput as String

For i = 1 to 4 'Has 4 sections so loops 4 times
strSQL = "Select * from Part " & i
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
While Not .EOF '<<<---Loops the number of records in the Set
strSQLOutput = strSQLOutput & rs!Part '<<<---Adjust for that
somehow
rs.MoveNext
rs.Close
Wend
MsgBox ("Section " & i & " Complete...")
Next
Set rs = Nothing
-------------------------------------------------

It is off the top of my head and I'm sure it has errors to adjust for but
that gives the general idea.
This makes the script a *lot* shorter and easier to follow.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Hey guys. I have the following script:

 

[ToddRhodesECI]

An update:

In safe mode with no AntiVirus running, this works on my laptop. Not
sure if that helps anything, but I thought I should update as I found
new info, if for no other reason than future searches.

Also, I ran Filemon during the Export process, I get a lot of instances
of Kaskpersky AntiVirus opening files right after I execute the code so
I'm fairly certain my answer lies with Kaspersky. I'll have to do some
more troubleshooting with my LAN admin tomorrow with Kaspersky shut
off.

Thanks again and any other ideas are more than welcome!

That is a heck of a lot simpler. I don't have that much formal training
in any programming languages so I'm still learning some of the tricks
of the trade.

As for the portion of code you're suspicious of, I have tried
everything there, the standard is just "C:\test.txt" but I wanted to
try a share on my laptop with unlimited permissions, just to
troubleshoot the file system aspects of the problem.

Thank you very much for the suggestion, I will use your idea in the
code. I can't get better without good pointers like yours. Now if I
could just write to the darned file on my laptop .

Todd.

I am suspicious of this.....:

Dim intFile As Integer
intFile = FreeFile <<<<----this
Open "\\eci-dzoflw13xtv\adi\test.txt" For Output As intFile <<<<---and
this

.......But I am not sure about it enough to suggest anything.

You could simplify the script a lot by just Dim'ing a single "strSQL" and
than dynamically concatinating the numerical digit on the end of "Select *
from Part1".
Like maybe:
---------------------------------------------------
Option Explicit
Dim i as Integer
Dim strSQL as String
Dim strSQLOutput as String

For i = 1 to 4 'Has 4 sections so loops 4 times
strSQL = "Select * from Part " & i
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
While Not .EOF '<<<---Loops the number of records in the Set
strSQLOutput = strSQLOutput & rs!Part '<<<---Adjust for that
somehow
rs.MoveNext
rs.Close
Wend
MsgBox ("Section " & i & " Complete...")
Next
Set rs = Nothing
-------------------------------------------------

It is off the top of my head and I'm sure it has errors to adjust for but
that gives the general idea.
This makes the script a *lot* shorter and easier to follow.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Hey guys. I have the following script:

 

[David F Cox]

Kapersky creates a log file so someone could check that. It is just possible
that if automatic repair is in effect it could have detected a false
positive and "fixed" it. One of the techniques virus writers use is to
disguise code as data so no anti-virus program can be free of this sort of
error. In traing mode you get the chance to tell it which programs it has
not got to "fix", but that can be a frustrating process.

An update:

In safe mode with no AntiVirus running, this works on my laptop. Not
sure if that helps anything, but I thought I should update as I found
new info, if for no other reason than future searches.

Also, I ran Filemon during the Export process, I get a lot of instances
of Kaskpersky AntiVirus opening files right after I execute the code so
I'm fairly certain my answer lies with Kaspersky. I'll have to do some
more troubleshooting with my LAN admin tomorrow with Kaspersky shut
off.

Thanks again and any other ideas are more than welcome!

That is a heck of a lot simpler. I don't have that much formal training
in any programming languages so I'm still learning some of the tricks
of the trade.

As for the portion of code you're suspicious of, I have tried
everything there, the standard is just "C:\test.txt" but I wanted to
try a share on my laptop with unlimited permissions, just to
troubleshoot the file system aspects of the problem.

Thank you very much for the suggestion, I will use your idea in the
code. I can't get better without good pointers like yours. Now if I
could just write to the darned file on my laptop .

Todd.

I am suspicious of this.....:

Dim intFile As Integer
intFile = FreeFile <<<<----this
Open "\\eci-dzoflw13xtv\adi\test.txt" For Output As intFile
<<<<---and
this

.......But I am not sure about it enough to suggest anything.

You could simplify the script a lot by just Dim'ing a single "strSQL"
and
than dynamically concatinating the numerical digit on the end of
"Select *
from Part1".
Like maybe:
---------------------------------------------------
Option Explicit
Dim i as Integer
Dim strSQL as String
Dim strSQLOutput as String

For i = 1 to 4 'Has 4 sections so loops 4 times
strSQL = "Select * from Part " & i
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
While Not .EOF '<<<---Loops the number of records in the Set
strSQLOutput = strSQLOutput & rs!Part '<<<---Adjust for
that
somehow
rs.MoveNext
rs.Close
Wend
MsgBox ("Section " & i & " Complete...")
Next
Set rs = Nothing
-------------------------------------------------

It is off the top of my head and I'm sure it has errors to adjust for
but
that gives the general idea.
This makes the script a *lot* shorter and easier to follow.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Hey guys. I have the following script:

 

[ToddRhodesECI]

It's all fixed up now. Kaspersky was blocking macro's from opening
files for Write access and I was able to turn that off and run the
script successfully. I now know why it failed the last time my LAN
Admin turn off the AntiVirus, apparently you have to close down the
application (Access 2003 in my case) and reopen it for Kaspersky to
treat it according to the new policy. Otherwise, it just keeps blocking
the script.

Thanks for your help guys, I really appreciate it!

Todd.