Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2017
    Posts
    3

    Unanswered: Error 3211 - ONLY after doCmd.outputTo command is issued

    I have a reports database and am having no problems until I issue the following code to export the contents of a table to a spreadsheet then run code to insert data into a temp table.

    **************************
    Private Sub btnXLS_Click()
    DoCmd.SetWarnings False

    Dim xtable As String
    Dim xtableExport As String

    xtable = "tempReportGrouping6A"
    xtableExport = "tempexport"

    Dim strSQL As String
    strSQL = "SELECT xField1 as [" & [Forms]![frmReports]![ddlGroup1] & "], actualhours, availablehours, forecasthours INTO " & xtableExport
    strSQL = strSQL & " FROM " & xtable

    DoCmd.RunSQL strSQL

    DoCmd.OutputTo acOutputTable, xtableExport, acFormatXLS, , False

    strSQL = ""
    xtable = ""
    xtableExport = ""

    End Sub
    ***************

    So that code works fine every time. But after running it once, if I then run the code to repopulate the xtable, I get this error:

    Run-time error '3211': The database could not lock table 'tempReportGrouping6A' because it is already in use by another person or process

    (Note that the table that is being executed (xtableExport) is NOT the same table that I am getting the 3211 error message for.)

    Here is the applicable section of the code that re-populates that temp table:


    ***********************************

    Dim xtablegroup As String
    xtablegroup = ""

    Dim xreportName As String

    xreportName = "Actual Forecast Available Group1"
    xtablegroup = "tempReportGrouping6A"

    sqlstring = "SELECT * INTO " & xtablegroup & " FROM ReportGroupingTemplate"
    DoCmd.RunSQL (sqlstring)

    ***** And this last "DoCmd.RunSQL (sqlstring)" is where I am getting the 3211 error.

    I can run this section of code over and over without any errors at all as long as I haven't tried to export a table with the DoCmd.outputTo command. Once I run that command, I have to completely shut down the database and re-open to get rid of the locks on the table. BUT the table that has the locks isn't the table I'm exporting in that command. I'm baffled.

    Also, I am the only one using this database when I get the error and there are no open tables or reports - just 3 forms that don't have anything to do with the tables in question.

    Can anyone help!??

    Thanks in advance!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    export to a spreadsheet using
    docmd.transferspreadsheet....

    Docmd.RUNSQL is for action queries, NOT select query.
    use a query , then DOCMD.OPENQUERY myqueryname

  3. #3
    Join Date
    Mar 2017
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    export to a spreadsheet using
    docmd.transferspreadsheet....

    Docmd.RUNSQL is for action queries, NOT select query.
    use a query , then DOCMD.OPENQUERY myqueryname
    Thanks for your feedback, ranman256!

    I actually am running an action query to create a new table based off of an existing table template. It isn't a select query. I there another way to create a table with a variable name without using DoCmd.runSQL?

    Thanks for the help!

  4. #4
    Join Date
    Mar 2017
    Posts
    3
    I think I have solved this issue.

    Instead of using the DoCmd.RunSql (sqlstring)

    I'm using:

    SQLRun (sqlstring)

    And the table locking issue is GONE!

    Thanks Ranman256!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •