need to enter data using loops in sql

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a table with 4 columns (DEPTID--int
SLOTID--int,DATE---date,AVAILABILITY--YES/NO) . WHERE COMBO(DEPTID,SLOTID,DATE) FORMS A PRIMARY KEY. EACH Department has (1,2,3,4----10) slots on a particular DATE. default value of availability column is 'yes'. i need to run a loop by which data can be entered into the database table instead of entering each record

any suggestions please do forward it to me. urgently needed.
 
Rahil,

This should do it for you.

Public Function CreateSlots(lDeptID As Integer, dDate As Date) As Boolean
Dim ws As Workspace
Dim db As Database
Dim sSQL1 As String
Dim sSQL2 As String
Dim iCtr As Integer

On Error GoTo Proc_Err

'Create the object references
Set ws = DBEngine(0)
Set db = CurrentDb

'Open the transaction
ws.BeginTrans

'Initialise the base SQL clause
sSQL1 = "INSERT INTO tblMyTable (DEPTID, SLOTID, [DATE]) VALUES (~)"

'Start cycling through the SlotIDs
For iCtr = 1 To 10
'Initialise the 2nd part of the SQL
sSQL2 = lDeptID & "," & iCtr & ",#" & Date & "#"

'Join the two SQL statements, and execute the query
db.Execute Replace(sSQL1, "~", sSQL2), dbFailOnError
Next iCtr

'If we got here, everything went OK - Commit the transaction
ws.CommitTrans
'Return success
CreateSlots = True

Proc_Exit:
'Clean up & exit
On Error Resume Next
Set db = Nothing
Set ws = Nothing
Exit Function

Proc_Err:
'An error occurred - rollback the transaction
ws.Rollback
'Return failure
CreateSlots = False
Resume Proc_Exit
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

rahil said:
i have a table with 4 columns (DEPTID--int,
Click to expand...
SLOTID--int,DATE---date,AVAILABILITY--YES/NO) . WHERE
COMBO(DEPTID,SLOTID,DATE) FORMS A PRIMARY KEY. EACH Department has
(1,2,3,4----10) slots on a particular DATE. default value of availability
column is 'yes'. i need to run a loop by which data can be entered into the
database table instead of entering each record.
 
Back
Top