-----Original Message-----
OK looks like you are attempting to insert a string (strVol) which is not
wrapped in quotes in the VALUES() sectionof the Insert Into Sql statement.
Here is the Code I used to make it work:
***** Start Code *****
Private Sub cmdWithLoop_Click()
Dim StrVol As String, strSql As String
Dim i As Integer, intPage As Integer
intPage = 2
StrVol = "Ron W"
For i = 1 To 12
strSql = "INSERT INTO tblProduction (wellid, prodmonth, prodyear,
vol, page, emplid, dicreated, dimodifiedc) " & _
"VALUES (" & Me.wellID & ", " & i & ", " & Me.EnterYear & ",
'" & FixQuotes(StrVol) & "', " & intPage & ", " & Me.emplID & _
", #" & Now() & "#, #" & Now() & "#)"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
Next i
End Sub
Private Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql Update query
Dim i As Integer, strOut As String
strOut = ""
For i = 1 To Len(strQuoted)
If Mid(strQuoted, i, 1) = Chr(39) Then
strOut = strOut & Chr(39)
End If
strOut = strOut & Mid(strQuoted, i, 1)
Next
FixQuotes = strOut
End Function
***** End Code *****
Note the Sql statement now has strVol wraped in in a function FixQuotes()
which is also wraped in Single quotes. Your problem was in that you were
attempting to insert as string that was not wraped in quotes. The
FixQuotes() function doubles up any single quotes it finds in strVol which
would create an error in the Sql statement.
Since the Month is the only field that changes there is even a faster way to
do this query without having to use a For Loop, but it requires another
table (tblMonth) that has at least one field (MonthNum) with 12 records
(Int's) from 1 to 12. Here is a function that uses a slightly different Sql
syntax for Insert Into. If you really need the speed and the month is the
only changing variable loose the loop and go with the extra table.
***** Start Code *****
Private Sub cmdNoLoop_Click()
Dim StrVol As String, strSql As String
Dim intPage As Integer
intPage = 2
StrVol = "Ron W"
strSql = "INSERT INTO tblProduction ( WellID, ProdMonth, ProdYear, Vol,
Page, EmplID, DICreated, DIModifiedc ) " _
& "SELECT " & Me.wellID & ", MonthNum, " & Me.EnterYear & ", '" &
FixQuotes(StrVol) & "', " & intPage & ", " & Me.emplID & _
", #" & Now() & "#, #" & Now() & "# " _
& "FROM tblMonth;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
End Sub
***** End Code *****
Ron W
Here is the DDL for the table:
CREATE TABLE [dbo].[tblProduction] (
[ProdID] [int] IDENTITY (1, 1) NOT NULL ,
[WellID] [int] NULL ,
[ProdMonth] [int] NULL ,
[ProdYear] [int] NULL ,
[Allowable] [int] NULL ,
[GasProd] [int] NULL ,
[OilProd] [int] NULL ,
[Vol] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Page] [int] NULL ,
[EmplID] [int] NULL ,
[DICreated] [datetime] NULL ,
[DIModifiedc] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
CONSTRAINT [DF__Temporary__WellI__1DE57479]
DEFAULT (0) FOR [WellID],
CONSTRAINT [DF__TemporaryU__Page__1ED998B2]
DEFAULT (0) FOR [Page],
CONSTRAINT [DF__Temporary__EmplI__1FCDBCEB]
DEFAULT (0) FOR [EmplID],
CONSTRAINT [aaaaatblProduction_PK] PRIMARY KEY
NONCLUSTERED
(
[ProdID]
) ON [PRIMARY]
GO
CREATE INDEX [ProdID] ON [dbo].[tblProduction] ([ProdID])
ON [PRIMARY]
GO
CREATE INDEX [UserID] ON [dbo].[tblProduction] ([EmplID])
ON [PRIMARY]
GO
CREATE INDEX [WellID] ON [dbo].[tblProduction] ([WellID])
ON [PRIMARY]
GO
***
Here is the actual code (I was using a trimmed down
version for my original post)
Slow, but "GOOD":
rst.Open "tblProduction", con, adOpenKeyset,
adLockOptimistic, adCmdTable
For i = 1 To 12
rst.AddNew
rst![WellID] = Me.WellID
rst![ProdMonth] = i
rst![ProdYear] = Me.EnterYear
rst![Vol] = strvol
rst![Page] = intpage
rst![EmplID] = Me.EmplID
rst![DICreated] = Now()
rst![DIModifiedc] = Now()
rst.Update
Next i
rst.Close
This is the fast, but "BAD" (freezes all machines):
For i = 1 To 12
strSQL = "INSERT INTO tblProduction (wellid,
prodmonth, prodyear, vol, page, emplid, dicreated,
dimodifiedc) " & _
"VALUES (" & Me.WellID & ", " & i
& ", " & Me.EnterYear & ", " & strVol & ", " & intPage
& ", " & Me.EmplID & _
", #" & Now() & "#, #" & Now() & "#)"
DoCmd.RunSQL strSQL
Next i
Again, thanks for taking a look at this. I'm totally
stumped!
-----Original Message-----
Yikes!
Based on the Sql statement I am assuming that all of
the
fields "Val1",
"prodMonth", and "prodYear" are numeric. If this is
the
case then I can not
see any reason for this query to fail. If you post the DDL to for the Sql
Table tblProduction I will try to reproduce that
problem
here.
Ron W
Sorry - just wrote the example wrong. The field names are
actually "prodMonth" and "prodYear".
-----Original Message-----
Year and Month are Reserved words in SQL (they are
Functions that take an
argument) . Try
strSQL = "INSERT INTO tblProduction (val1, [month],
[year]) VALUES (" &
Me.Val1 & ", " & i & ", " & Me.EnterYear)"
Ron W
message
I have an Access2000 front end on 50 computers linked
to a
SQLServer2000 backend. I need to insert monthly records
for the chosen year when the related parent record is
selected. I first used the open recordset & addnew
method
to insert the records as below:
rst2.Open strSQL2, con, adOpenKeyset, adLockOptimistic,
adCmdText
For i = 1 To 12
rst2.AddNew
rst2![val1] = Me.val1
rst2![month] = i
rst2![Year] = Me.EnterYear
rst2.Update
Next i
End If
rst2.Close
This is obviously very slow when 50 people are entering
data. However, it didn't cause the SQL server to
freeze.
So to improve performance I switched to the following:
For i = 1 To 12
strSQL = "INSERT INTO tblProduction (val1, month,
year)
VALUES (" & Me.Val1 & ", " & i & ", " &
Me.EnterYear)"
DoCmd.RunSQL strSQL
Next i
This is definitely faster (GOOD) but causing the SQL
Server to lock up and all the users are frozen (BAD!).
Any suggestions as to why this problem is
occurring
and
if
there is a better way to handle this.
Thanks!
.
.
.
