By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,905 Members | 1,616 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,905 IT Pros & Developers. It's quick & easy.

Creating a Table from with in a Recordset?

P: 2
Here is what I am trying to do. Kindly, help me.
1. I'm creating a query dynamically based on User input using VBA
(strSQL and DotSQL in the code below)
2. Executing the created query with in VBA
3. Writing to a table (tblRandom) the values from the recordset.

--------------------------------
Private Sub append_record_Click()
Dim strSQL As String
Dim dotsql As String
Dim inp As Recordset
Dim test As Recordset
Dim how_many As Integer
Dim i As Integer
Dim bat_cnt As Integer
Dim cnn1 As New Connection
Dim companyID As Integer
Dim employeeID As Integer

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = D:\temp\DRUG_ALCOHOL_DATA.MDB;"


'************************************************* **********************
'This is the code if the test group is NOT For the 200* Consortium
'************************************************* **********************
strSQL = "SELECT TOP " & how_many & " tblEMP.COMPANY_ID, tblEMP.EMPLOYEE_ID, Rnd(tblEMP.EMPLOYEE_ID) AS RANDOM_NO " & _
"FROM tblEMP " & _
"WHERE ((tblEMP.COMPANY_ID = " & Me.COMPANY_ID & ") AND (tblEMP.INACTIVE_DATE Is Null) " & _
"and ((tblEMP.DOT_CLASS) IS NULL OR (tblEMP.DOT_CLASS) <> ""DOT"")) " & _
"ORDER BY Rnd(tblEMP.EMPLOYEE_ID);"

'DoCmd.RunSQL "CREATE TABLE tblTest ([EMPLOYEE_ID] " & _
'"CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), " & _
'"[LastName] TEXT(30), [BirthDate] DATETIME);"



'************************************************* **********************
'This is the code if the test group IS For the 200* Consortium
'************************************************* **********************
dotsql = "SELECT TOP " & how_many & " tblCOMPANY.COMPANY_ID, tblEMPLOYEE.EMPLOYEE_ID, Rnd(tblEMPLOYEE.EMPLOYEE_ID) AS RANDOM_NO" & _
" FROM (tblCOMPANY INNER JOIN tblCOMPANY_TEST ON tblCOMPANY.COMPANY_ID = tblCOMPANY_TEST.COMPANY_ID) " & _
"INNER JOIN tblEMPLOYEE ON tblCOMPANY.COMPANY_ID = tblEMPLOYEE.COMPANY_ID" & _
" WHERE (((tblCOMPANY_TEST.TEST_GROUP_NAME)= " & Me.CONSORTIUM_NAME & ") AND ((tblEMPLOYEE.DOT_CLASS)= ""DOT"") " & _
"AND ((tblEMPLOYEE.INACTIVE_DATE) Is Null)) " & _
"ORDER BY Rnd(tblEMPLOYEE.EMPLOYEE_ID)"

Set inp = New ADODB.Recordset
inp.CursorType = adOpenKeyset
inp.LockType = adLockOptimistic



'************************************************ **********************
' If the test is for the 200* Consortium, then use the dot sql statment, else use the other
'************************************************* *********************

If Me.COMPANY_ID.Value = 122 Then
inp.Open dotsql, cnn1
Else
inp.Open strSQL, cnn1
End If

Set test = New ADODB.Recordset
test.CursorType = adOpenKeyset
test.LockType = adLockOptimistic
test.Open "tblQRTLY_TESTING", cnn1
'New Declarations
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim InsertSQL As String
Dim cnCh5 As ADODB.Connection
Set cnCh5 = New ADODB.Connection
cnCh5.Open cnn1
'Dim the objects
Dim tdf As ADOX.Table
Dim idx As ADOX.Index
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog

cat.ActiveConnection = CurrentProject.Connection
Set tdf = New ADOX.Table

With tdf
.Name = "tblRandom"
Set .ParentCatalog = cat
.Columns.Append "", adInteger
.Columns

-------------------------------------------------------------------------------------
Loop thorough the Recordset and build a new table
-------------------------------------------------------------------------------------

i = 1
inp.MoveFirst
Do Until inp.EOF
MsgBox (" In loop")
test.AddNew
test!COMPANY_ID = inp!COMPANY_ID
MsgBox test!COMPANY_ID
test!EMPLOYEE_ID = inp!EMPLOYEE_ID
companyID = inp!COMPANY_ID
employeeID = inp!EMPLOYEE_ID
MsgBox test!EMPLOYEE_ID
test!TEST_GROUP = Me!TEST_GROUP
test!TEST_CATEGORY = Me!TEST_CATEGORY
If Me!TEST_CATEGORY = "BAT" Then
test!TEST_REASON = "Breath Alcohol"
Else
test!TEST_REASON = "Random"
End If
test!QUARTER = Me!QUARTER
test!YEAR = Format(Now(), "YYYY")
test!SAMPLE_DATE = Date

-----------------------------------------------------
SQL to execute to write into tblRandom
-------------------------------------------------------

InsertSQL = "INSERT INTO tblRandom ([EMPLOYEE_ID], [COMPANY_ID]) " & _
"VALUES (" & test!EMPLOYEE_ID & "," & test!COMPANY_ID & ");"

Set cmdCommand.ActiveConnection = cnCh5

cmdCommand.CommandText = InsertSQL
cmdCommand.Execute

test.Update
inp.MoveNext

i = i + 1
Loop
MsgBox "You have successfully selected " & how_many & " Employees for " & Me!TEST_CATEGORY & " Testing!"

'Set rsRandom = New ADODB.Recordset
'With rsRandom
' .CursorType = adOpenKeyset
' .CursorLocation = adUseClient
' .LockType = adLockOptimistic
' .Open "tblRandom", cnCh5
'End With



inp.Close
test.Close
Set inp = Nothing
Set test = Nothing

End Sub
Feb 25 '07 #1
Share this Question
Share on Google+
3 Replies


100+
P: 1,646
Hi. Would you please let us know what your problem is with this code. It would take some time to try and walk through every line.
Feb 25 '07 #2

P: 2
Hi. Would you please let us know what your problem is with this code. It would take some time to try and walk through every line.
Thanks for responding to my Question. Actually, the problem is that the code is not able to Insert values read from the RecordSet into the table tblRandom. Although, I can see the values being read from the RecordSet which in my case are Company_ID and Employee_ID.

In the code snippet below the problem seems to occur at the statement.

cmdCommand.Execute

Here is error I get:-
************************************
"Run time error 2147217865 (80040e37)
Could not find output table 'tblRandom'.
***************************************

Code snippet
--------------------
-------------------------------------------------------------------------------------
Loop thorough the Recordset and build a new table
-------------------------------------------------------------------------------------

i = 1
inp.MoveFirst
Do Until inp.EOF
MsgBox (" In loop")
test.AddNew
test!COMPANY_ID = inp!COMPANY_ID
MsgBox test!COMPANY_ID
test!EMPLOYEE_ID = inp!EMPLOYEE_ID
companyID = inp!COMPANY_ID
employeeID = inp!EMPLOYEE_ID
MsgBox test!EMPLOYEE_ID
test!TEST_GROUP = Me!TEST_GROUP
test!TEST_CATEGORY = Me!TEST_CATEGORY
If Me!TEST_CATEGORY = "BAT" Then
test!TEST_REASON = "Breath Alcohol"
Else
test!TEST_REASON = "Random"
End If
test!QUARTER = Me!QUARTER
test!YEAR = Format(Now(), "YYYY")
test!SAMPLE_DATE = Date

-----------------------------------------------------
SQL to execute to write into tblRandom
-------------------------------------------------------

InsertSQL = "INSERT INTO tblRandom ([EMPLOYEE_ID], [COMPANY_ID]) " & _
"VALUES (" & test!EMPLOYEE_ID & "," & test!COMPANY_ID & ");"

Set cmdCommand.ActiveConnection = cnCh5

cmdCommand.CommandText = InsertSQL
cmdCommand.Execute

test.Update
inp.MoveNext

i = i + 1
Loop
MsgBox "You have successfully selected " & how_many & " Employees for " & Me!TEST_CATEGORY & " Testing!"




inp.Close
test.Close
Set inp = Nothing
Set test = Nothing
Feb 25 '07 #3

100+
P: 1,646
The error suggests that tblRandom is not in DRUG_ALCOHOL_DATA.MDB
Perhaps it is spelled slightly differently.
Feb 25 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.