Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Accessing MS Access 2003 from VB6

It has been a long time since I wrote any code so please excuse the naming conventions and the novice coding.
In VB6 I have successfully opened an MS Access 2003 database with the following code

Public Sub OpenDatabase()
Dim Msg As String

DbOpened = True
MyFile = App.Path & "\PrintDir.MDB" ' Define name of database.
On Error GoTo OpenDatabase_eh
Set MyWorkspace = Workspaces(0)

' Open database.
Set MyDatabase = MyWorkspace.OpenDatabase(MyFile)
Exit Sub

Later on I successfully update the database with the following code

Public Function UpdateDatabase(strFileName As String)
Dim intErrNbr As Integer
Dim Msg As String
Dim strErr As String
Dim a As Integer
On Error GoTo UpdateDatabase_eh

If strFileName = "" Then
Exit Function
End If
BeginTrans
Set MyTable = MyDatabase.OpenRecordset("FileInfo", dbOpenDynaset)

MyTable.AddNew
MyTable![FileName] = strFileName
.
.
.
MyTable.Update

gstrFileName = ""
glngFileSize = 0
gvarFileDateCreated = ""
gstrFileComments = ""

CommitTrans
MyTable.Close

Exit Function

UpdateDatabase_eh:

intErrNbr = Err
strErr = Error
Msg = "Err = " & intErrNbr & ", Error = " & strErr & vbCrLf & vbCrLf
Msg = Msg & " " & gstrFileName
MsgBox Msg, vbCritical
Rollback
On Error GoTo 0

End Function

However, when I try to delete the table entries, not the table, before I update the table I get error 2075, "The operation requires an open database".

Private Function DeleteTableContents()
Dim strMsg As String
Dim intAns As Integer
Dim DSQL As String
Dim intDCount As Integer
Dim xclSQL As String

On Error GoTo DeleteTableContents_eh
Set MyTable = MyDatabase.OpenRecordset("FileInfo", dbOpenDynaset)
DSQL = "DELETE * FileInfo.FileName FROM FileInfo "
DSQL = DSQL & "WHERE Not FileInfo.FileName = Xtest.jpg" '& "isnull"
'doCmd.SetWarnings False
DoCmd.RunSQL DSQL
'doCmd.SetWarnings True
MyTable.Close
Exit_DeleteTableContents:
Exit Function
DeleteTableContents_eh:
MsgBox "DeleteTableContents - Error Number = " & Err.Number & ", Error Description = " & Err.Description
Resume Exit_DeleteTableContents

End Function

Can anyone see what I am missing?
Thanks

Answers

Sign In or Register to comment.