[MS_AccessPros] RE: Run-time error ‘3010’ Tab le ‘tblName’ already exist

 

Phucon 

First thing to do is to comment out all your On Error lines so you can debug the code. Then put a break point on this line:

If Not BuildResultsTable(strSql, "tblResults", lngRecAffected) Then

When the code breaks press F8 to step through each line so you can see what is going on. If the error occurs on the table Delete line just move the highlighter down to the next line of code and continue pressing F8.

Just some advice...

Dim your DAO objects like this to prevent ambiguity:

Dim db As DAO.Database

Dim qdfAction As DAO.QueryDef

And you might want to use an SQl statement to delete the table. I don't think it matters, but it might help.

db.Execute "DROP TABLE " & strTableName

--- In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

I have a Search Form that calls the function (which I copied from a book) as shown below.  It generates a "Run-time error  '3010' Table 'tblName' already exist." every other time. e.g.(the 1^st time ok, the 2^nd time will be an error, then the 3^rd time ok again then the 4^th will be error…) the function's called.

Logically it will delete the table each time the function is called, is that right? What caused it to skip only deletes the table every other time?

Phucon.

Function BuildResultsTable(strSql As String, strTableName As String, lngRecAffected As Long)

Dim db As Database

Dim qdfAction As QueryDef

Set db = CurrentDb

On Error Resume Next     'ignore any erors that may occur when deleting a table.

db.TableDefs.Delete strTableName

Debug.Print strTableName

On Error GoTo 0     'ignore errors in any subsequent lines of code.

strSql = Replace(strSql, " FROM ", " INTO " & strTableName & " FROM ")

Debug.Print strSql

Set qdfAction = db.CreateQueryDef("", strSql)

qdfAction.Execute dbFailOnError

lngRecAffected = qdfAction.RecordsAffected

qdfAction.Close

BuildResultsTable = True

End Function

 

Private Sub cmdFind_Click()

Dim strSql As String

Dim lngRecAffected As Long

If Not BuildResultsTable(strSql, "tblResults", lngRecAffected) Then

     MsgBox "There was a problem building the SQL string", vbInformation, "Error"

     Exit Sub

End If

Set rs = CurrentDb.OpenRecordset("qryResults", dbOpenSnapshot)

If Not rs.RecordCount = 0 Then

     DoCmd.OpenForm FormName:="frmPledgeeShowData2", view:=acNormal

Else

     If MsgBox(cErrMsg & Left$(Mid$(TempVars!stringwhere, 8), 15) & ".  Add new record?", vbYesNo +                                                vbExclamation, "Find Record") = vbYes Then

              DoCmd.OpenForm FormName:="frmPledgeeDataEntry2", view:=acNormal

     Else

          Exit Sub

     End If

End If

End Sub

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Members 5

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___