asked on Insert Into, 3134 syntax error
I have a module I got from an EE contributor that I've been using for a while to calculated the size of each object (tables, forms etc..) in an Access DB.
I've used it many times on my Win XP Access 2003 MDB's and I'm trying to run it for the first time on my Access 2010, Win 7 Pro DB.
I'm getting a syntax error on the 'Insert Into' command.
This is the statement that throws the error:
I did a debug.print to see what the string it was building looked like and this is it:
I checked how it runs on my XP Access 2003 MDB's and it still works fine and creates exactly the same string for the 'Insert Into'
Any ideas>?
I've used it many times on my Win XP Access 2003 MDB's and I'm trying to run it for the first time on my Access 2010, Win 7 Pro DB.
I'm getting a syntax error on the 'Insert Into' command.
This is the statement that throws the error:
DoCmd.RunSQL "Insert Into ObjectSizes (Name, Type, Size) Values ('" & objName & "','" & strObject & "'," & FileLen(strDbName) - blankSize & ")"
I did a debug.print to see what the string it was building looked like and this is it:
Insert Into ObjectSizes (Name, Type, Size) Values ('frmConversion','Forms',135168)
I checked how it runs on my XP Access 2003 MDB's and it still works fine and creates exactly the same string for the 'Insert Into'
Any ideas>?
Microsoft Access
Last Comment
Hamed Nasr
I created an ObjectSizes table in my access 2010 DB, with those three fields, and ran the "insert into" statement you posted from debug.print, without error.
However, when I created the NAME field in the table, it gave me a "reserved word" warning, perhaps this is the problem? Does the table already exists when you run this command?
However, when I created the NAME field in the table, it gave me a "reserved word" warning, perhaps this is the problem? Does the table already exists when you run this command?
ASKER
Thanks for your response and Merry Christmas!
The table exists when I run the command, it is created in this subroutine.
Here is the whole subroutine:
The table exists when I run the command, it is created in this subroutine.
Here is the whole subroutine:
Public Sub GetObjectSizes()
'*************************************************************************************************
' Purpose: Routine to get the sizes of tables, forms, reports and modules in the current database.
' Usage: After running this routine, look at table ObjectSizes.
'*************************************************************************************************
On Error GoTo CopyErr
DoCmd.SetWarnings False
Dim i As Integer, k As Integer, intObjectType As Integer
Dim strObject As String, strDbName As String, objName As String
Dim clearArr As Variant
Dim blankSize As Long
Dim DOC As Document
Dim cont As Container
Dim wsp As Workspace
Dim db2 As Database
Dim tdf As TableDef
'C:\My Documents
strDbName = "C:\My Documents\MJOTemp.mdb"
clearArr = Array("Forms", "Reports", "Modules") 'doesn't work with Macros & queries
If DCount("*", "MSysObjects", "MSysObjects.Name='ObjectSizes'") > 0 Then
CurrentDb.TableDefs.Delete ("ObjectSizes")
End If
CurrentDb.Execute "CREATE TABLE ObjectSizes (" _
& "Name TEXT(255), " _
& "Type TEXT(10), " _
& "Size LONG)"
' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)
'Get size of blank database
If Dir(strDbName) <> "" Then Kill strDbName
Set db2 = wsp.CreateDatabase(strDbName, dbLangGeneral)
db2.Close
Set db2 = Nothing
blankSize = FileLen(strDbName)
Kill strDbName
'Iterate to get sizes of objects (Forms, Reports, & Modules)
For i = 0 To UBound(clearArr)
For Each cont In CurrentDb.Containers
If cont.Name = clearArr(i) Then
For Each DOC In cont.Documents
'create database
Set db2 = wsp.CreateDatabase(strDbName, dbLangGeneral)
strObject = clearArr(i)
objName = Replace(DOC.Name, "'", "")
intObjectType = Switch(strObject = "Tables", 0, strObject = "Queries", 1, strObject = "Forms", 2, strObject = "Reports", 3, strObject = "Modules", 5)
DoCmd.TransferDatabase acExport, "Microsoft Access", strDbName, intObjectType, objName, objName
db2.Close
Set db2 = Nothing
DoCmd.RunSQL "Insert Into ObjectSizes (Name, Type, Size) Values ('" & objName & "','" & strObject & "'," & FileLen(strDbName) - blankSize & ")"
Kill strDbName
Next DOC
End If
Next cont
Next i
'Iterate to get sizes of tables
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" And tdf.Attributes = 0 Then
Set db2 = wsp.CreateDatabase(strDbName, dbLangGeneral)
objName = Replace(tdf.Name, "'", "")
DoCmd.TransferDatabase acExport, "Microsoft Access", strDbName, 0, objName, objName
db2.Close
Set db2 = Nothing
DoCmd.RunSQL "Insert Into ObjectSizes (Name, Type, Size) Values ('" & objName & "','Tables'," & FileLen(strDbName) - blankSize & ")"
Kill strDbName
End If
Next
MsgBox "Done"
DoCmd.SetWarnings True
Exit Sub
CopyErr:
If Err.Number = 3420 Then
For k = 1 To 200: DoEvents: Next 'Pause needed if TransferDatabase was unsuccessful because of this error number; otherwise, database crashes database.
Resume
ElseIf Err.Number = 2007 Then
MsgBox "The object " & objName & " is still open", vbInformation + vbOKOnly, strObject
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Try to change table field names, as objName, objType, objSize for example.