Error 3163 field too small....needs memo field?

[wmrainer]

I'm getting that error 3163 when running my script. I know it's because the table needs to have a memo field instead. However, every time I run the script it's changing my field from memo back to text and therefore giving the error!

Debug pulls this piece-

rstInsert![CLASSINFO] = RememberNames

I think if I can just put somewhere in the code that that particular field needs to be memo I would be all good. Can anyone tell me how to add that? I need to get this finished today so any help soon would be awesome!!!!
Many many thanks!

 

[SOS]

Instead of deleting the table, just empty it and then use an APPEND query instead of a make table query to fill it. That way you can define the fields the way you want and they won't change.

 

[wmrainer]

Hi again SOS, You've been great to work with BTW!

Could you help me with the code? I'm REALLY new at this insanity!

 

[SOS]

just create a delete query. The simple SQL is

Delete * From TableNameHere


and then execute.

You can create an Append query in the QBE grid (the design view area of the queries) and when you click on the APPEND query type on the Ribbon, it will ask you which table you want to append to. Save that query and then execute it.

 

[wmrainer]

So I feel crazy dumb here....The code I have already has the delete table which is what is causing the issue. Isn't there a simple way to delete the data from the table, but not the actual table each time?

 

[SOS]

So I feel crazy dumb here....The code I have already has the delete table which is what is causing the issue. Isn't there a simple way to delete the data from the table, but not the actual table each time?

Get rid of this line:

Call DeleteTable("MCEMAIL")

And then use

CurrentDb.Execute "Delete * From MCEMAIL", dbFailOnError

 

[wmrainer]

Ok so I've tweaked it and added a few other things I needed to and it works great up until this point....

dbs.Execute ("SELECT DISTINCT CUSTNO, FULLNAME, EMAIL, REGID, '' AS CLASSINFO INTO MCEMAIL " _
  & "FROM MCTEST2")

Error I get is: 3010 Table'MCEMAIL' already exists.

It seems that the way this particular piece was written originally was to have it create the table at this point. Does that seem right? If it is, is there a way to just change this piece of the code? I'm so CLOSE>>>:banghead:

full code is attached if you need it

 

[SOS]

It would be

Dim strSQL As String
 
strSQL = "INSERT INTO MCEMAIL (CUSTNO, FULLNAME, EMAIL, CLASSINFO) " & _
"SELECT DISTINCT CUSTNO, FULLNAME, EMAIL, REGID " &  _
"FROM MCTEST2"
dbs.Execute strSQL, dbFailOnError

 

[wmrainer]

When I put that in it's place it throws off the rest of the code and I get runtime error 3075 syntax error (missing operator).

And when I open the table, it shows info that supposed to be in CLASSINFO field in REGID field.

 

[SOS]

When I put that in it's place it throws off the rest of the code and I get runtime error 3075 syntax error (missing operator).

And when I open the table, it shows info that supposed to be in CLASSINFO field in REGID field.

I don't have your full structure so I had to write it as best as I could from what you had. The way I laid it out it should be easy to spot the problem as far as the fields go. I'm not sure what the syntax problem is unless there needs to be parentheses around the SELECT statement.

 

[wmrainer]

Here's the whole code. I'll be back at work in an hour or so and will see what I can figure out.

Option Compare Database

Private Sub DeleteTable(Table_Navn As String)
  Dim dbs As Database, ctr As Container, doc As Document
  
  Set dbs = CurrentDb
  Set ctr = dbs.Containers!Tables
  For Each doc In ctr.Documents
    'Delete table if it exist.
    If UCase(doc.Name) = Table_Navn Then
      DoCmd.DeleteObject acTable, doc.Name
      Exit For
    End If
  Next doc
End Sub

Private Sub Form_Load()

End Sub

Private Sub Run_Query_Click()

End Sub

Private Sub RunQuery_Click()
  DoCmd.OpenQuery "MC TEST FINAL"
  DoCmd.OpenQuery "MC TEST2 again"
      
  Dim dbs As Database, rst As Recordset, rstInsert As Recordset, RememberNames As String
  
  Set dbs = CurrentDb
  DoCmd.RunSQL "DELETE * FROM MCEMAIL"
  dbs.Execute ("SELECT DISTINCT CUSTNO, FULLNAME, EMAIL, REGID, '' AS CLASSINFO INTO MCEMAIL " _
  & "FROM MCTEST2")

  Set rstInsert = dbs.OpenRecordset("MCEMAIL")
  If Not rstInsert.EOF Then
    Do
      RememberNames = ""
Set rst = dbs.OpenRecordset("SELECT CLASSNO, CUSTNO, FULLNAME, EMAIL, REGID " _
& "FROM MCTEST2 " _
& "WHERE CUSTNO=" & rstInsert![CUSTNO] & " AND FULLNAME='" & rstInsert![FullName] & "' AND EMAIL='" & rstInsert![EMAIL] & "' AND REGID=" & rstInsert![REGID])
      Do
        RememberNames = RememberNames & rst![CLASSNO] & "; "
        rst.MoveNext
      Loop Until rst.EOF
      rstInsert.Edit
      rstInsert![CLASSINFO] = RememberNames
      rstInsert.Update
      rstInsert.MoveNext
    Loop Until rstInsert.EOF
  End If
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MCEMAIL", "C:\MCEMAIL.xls", True
End Sub

Private Sub Detail_Click()

End Sub

Private Sub Command1_Click()

End Sub

Private Sub Run_Email_Query_Click()

End Sub

 

[SOS]

I missed the one field.

This should do it.

Dim strSQL As String
 
strSQL = "INSERT INTO MCEMAIL (CUSTNO, FULLNAME, EMAIL, REGID, CLASSINFO) " & _
"(SELECT DISTINCT CUSTNO, FULLNAME, EMAIL, REGID, NULL AS CLASSINFO " &  _
"FROM MCTEST2 " & _
"WHERE CUSTNO=" & rstInsert![CUSTNO] & " AND FULLNAME='" & _
rstInsert![FullName] & "' AND EMAIL='" & _
rstInsert![EMAIL] & "' AND REGID=" & rstInsert![REGID] & ")"
 
dbs.Execute strSQL, dbFailOnError

I highly suggest using the variable for the call because Access sometimes doesn't like that many characters in the actual Excecute statement outside of a variable. But I guess if it works for you, fine.