Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

error code 3734 in access vba

atksamy's Avatar
Member
  
Join Date: Oct 2008
Posts: 82
#1: Nov 13 '08
i have the following code and in the line no 19 i am getting runtime error 3734
" the database is locked by user name......cannot be opened or saved"

Expand|Select|Wrap|Line Numbers
  1. Sub plausibilitaet_check()
  2.  
  3. Dim rs As DAO.Recordset
  4. Dim rs2 As ADODB.Recordset
  5. Dim db As database
  6. Dim strsql As String
  7. Dim tdf As TableDef
  8.  
  9.  
  10.  
  11.  
  12. Set db = opendatabase("C:\Codebook.mdb")
  13. Set rs = db.OpenRecordset("plausibilitaeten")
  14.  
  15. Set rs2 = CreateObject("ADODB.Recordset")
  16. rs2.ActiveConnection = CurrentProject.Connection
  17.  
  18.  
  19. For Each tdf In CurrentDb.TableDefs
  20.  
  21.  
  22.  
  23.  
  24.    If Left(tdf.Name, 4) <> "MSys" Then
  25.         rs.MoveFirst
  26.         strsql = "SELECT * From [" & tdf.Name & "] WHERE "
  27.  
  28.         Do While Not rs.EOF
  29.             On Error Resume Next
  30.             Debug.Print tdf.Name
  31.             rs2.Open strsql & " " & rs![plausibilitaet_alt]
  32.             If Err.Number = 0 Then
  33.                 On Error GoTo 0
  34.                 If Not rs2.EOF Then
  35.                     Debug.Print rs![Error]
  36.                     Debug.Print rs2.GetString
  37.                 End If
  38.             End If
  39.             Err.Clear
  40.             rs2.Close
  41.             rs.MoveNext
  42.  
  43.         Loop
  44.     End If
  45. Next
  46. End Sub

can someone tell me what am i doing wrong

NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#2: Nov 14 '08

re: error code 3734 in access vba

This indicates that someone, somewhere, has the database open exclusively. This can be done manually, or it will happen automatically when anyone makes design changes to any of the objects.

Is "user name ....." referring to you or another user?

If the worst comes to the worst and you can't get it to clear down even when everyone has stopped using it then find and delete the .LDB file associated with the .MDB. Be careful not to do this when anyone is using the .MDB if at all possible.
atksamy's Avatar
Member
  
Join Date: Oct 2008
Posts: 82
#3: Nov 14 '08

re: error code 3734 in access vba

hmmm funny i am the only one using this and today suddenly the error is not coming
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#4: Nov 14 '08

re: error code 3734 in access vba

In that case it was probably as simple as your just having made some design changes in the current session (which would have locked out the database for various items).
atksamy's Avatar
Member
  
Join Date: Oct 2008
Posts: 82
#5: Nov 18 '08

re: error code 3734 in access vba

THe code provided works fine when i run it as it is

but when i add the following lines of code

Expand|Select|Wrap|Line Numbers
  1. Dim objexcel As Excel.Application
  2.                      Dim wbexcel As Excel.Workbook
  3.                      Dim wbExists As Boolean
  4.                      Dim objSht As Excel.Worksheet
  5.                      Dim objRange As Excel.Range
  6.  
  7.  
  8.                      Set objexcel = CreateObject("excel.Application")
  9.                      objexcel.Visible = True
  10.                      On Error GoTo Openwb
  11.                      wbExists = False
  12.                      Set wbexcel = objexcel.Workbooks.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\reports\ERROR REPORT3.xls")
  13.                      Set objSht = wbexcel.Worksheets("Sheet1")
  14.                      objSht.Activate
  15.                      wbExists = True
  16. Openwb:
  17.  
  18.                      On Error GoTo 0
  19.                      If Not wbExists Then
  20.                      objexcel.Workbooks.Add
  21.                      Set wbexcel = objexcel.ActiveWorkbook
  22.                      Set objSht = wbexcel.Worksheets("Sheet1")
  23.  
  24.                      End If

in the beginning i get some parameter expected runtime error in line no 31 of the code given in post 1.

what i dont understand is there is no connection between the two sets of codes

th e exact runtime error is 2147217904(80040e10)
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#6: Nov 18 '08

re: error code 3734 in access vba

I'm struggling to understand what you've added where and whereabouts the error comes up in all of this.

Adding a chunk of code is not like simple arithmetic. It must be added at a certain point, or in some way that needs explaining.
atksamy's Avatar
Member
  
Join Date: Oct 2008
Posts: 82
#7: Nov 18 '08

re: error code 3734 in access vba

Here is the code

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub plausibilitaet_check()
  4.  
  5. Dim rs As DAO.Recordset
  6. Dim rs2 As ADODB.Recordset
  7. Dim db As database
  8. Dim strsql As String
  9. Dim tdf As TableDef
  10. Dim i, j As Integer
  11. i = 1
  12.  
  13.  
  14. 'OPEN excel DOCUMENT FOR WRITING REPORT
  15.  
  16.                      Dim objexcel As Excel.Application
  17.                      Dim wbexcel As Excel.Workbook
  18.                      Dim wbExists As Boolean
  19.                      Dim objSht As Excel.Worksheet
  20.                      Dim objRange As Excel.Range
  21.  
  22.  
  23.                      Set objexcel = CreateObject("excel.Application")
  24.                      objexcel.Visible = True
  25.                      On Error GoTo Openwb
  26.                      wbExists = False
  27.                      Set wbexcel = objexcel.Workbooks.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\reports\ERROR REPORT3.xls")
  28.                      Set objSht = wbexcel.Worksheets("Sheet1")
  29.                      objSht.Activate
  30.                      wbExists = True
  31. Openwb:
  32.  
  33.                      On Error GoTo 0
  34.                      If Not wbExists Then
  35.                      objexcel.Workbooks.Add
  36.                      Set wbexcel = objexcel.ActiveWorkbook
  37.                      Set objSht = wbexcel.Worksheets("Sheet1")
  38.  
  39.                      End If
  40.  
  41.  
  42. Set db = opendatabase("C:\Documents and Settings\TAYYAPP\Desktop\GIDAS_Codebook.mdb")
  43. Set rs = db.OpenRecordset("plausibilitaeten")
  44.  
  45. Set rs2 = CreateObject("ADODB.Recordset")
  46. rs2.ActiveConnection = CurrentProject.Connection
  47.  
  48.  
  49. For Each tdf In CurrentDb.TableDefs
  50.  
  51.    If Left(tdf.Name, 4) <> "MSys" Then
  52.         rs.MoveFirst
  53.         strsql = "SELECT * From [" & tdf.Name & "] WHERE "
  54.  
  55.         Do While Not rs.EOF
  56.             On Error Resume Next
  57.  
  58.             rs2.Open strsql & " " & rs![plausibilitaet_alt]
  59.             If Err.Number = 0 Then
  60.                 On Error GoTo 0
  61.                 If Not rs2.EOF Then
  62.  
  63.                             If tdf.Name <> "01umwelt" Then
  64.  
  65.  
  66.                             i = i + 1
  67.                             j = 1
  68.  
  69.                             objSht.Cells(i, j).Value = "Tabelle:  " & tdf.Name
  70.                             j = j + 1
  71.                             objSht.Cells(i, j).Value = "kombinationsschluessel: FALL " & rs2.Fields(0)
  72.                             j = j + 1
  73.                             objSht.Cells(i, j).Value = "Error:  " & rs![fehlermeldung]
  74.  
  75.  
  76.  
  77.                             End If
  78.                             'Debug.Print tdf.Name
  79.                             'Debug.Print rs![fehlermeldung]
  80.                             'Debug.Print rs2.Fields(0)
  81.                 End If
  82.             End If
  83.             Err.Clear
  84.             rs2.Close
  85.             rs.MoveNext
  86.  
  87.         Loop
  88.     End If
  89. Next
  90. End Sub

in line no 58 i get a runtime error 2147217904(80040e10) parameter missing

but if i run the code as given in post#1 it is fine
only when i add the code for opening excel i get this error
atksamy's Avatar
Member
  
Join Date: Oct 2008
Posts: 82
#8: Nov 19 '08

re: error code 3734 in access vba

THanks for having a look but i guess the on error statements were causing the problem.

i moved the excel part to another sub and now the code runs fine

thanks
anyway.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#9: Nov 19 '08

re: error code 3734 in access vba

I always feel the On Error ... commands in VBA are a bit of a bodge. They don't nest properly and are procedure relative.

When using such code it is often necessary to separate out functionality into logical procedures as you have done. It's nearly always the better design anyway mind you, having separate concepts in their own procedures, so it may be a bonus in disguise.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,471 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved