Accessing MS Access 2003 from VB6 - Programmers Heaven

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.