473,545 Members | 386 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating a Table from with in a Recordset?

2 New Member
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_C lick()
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=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source = D:\temp\DRUG_AL COHOL_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.EMPL OYEE_ID) AS RANDOM_NO " & _
"FROM tblEMP " & _
"WHERE ((tblEMP.COMPAN Y_ID = " & Me.COMPANY_ID & ") AND (tblEMP.INACTIV E_DATE Is Null) " & _
"and ((tblEMP.DOT_CL ASS) IS NULL OR (tblEMP.DOT_CLA SS) <> ""DOT"")) " & _
"ORDER BY Rnd(tblEMP.EMPL OYEE_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.COMP ANY_ID, tblEMPLOYEE.EMP LOYEE_ID, Rnd(tblEMPLOYEE .EMPLOYEE_ID) AS RANDOM_NO" & _
" FROM (tblCOMPANY INNER JOIN tblCOMPANY_TEST ON tblCOMPANY.COMP ANY_ID = tblCOMPANY_TEST .COMPANY_ID) " & _
"INNER JOIN tblEMPLOYEE ON tblCOMPANY.COMP ANY_ID = tblEMPLOYEE.COM PANY_ID" & _
" WHERE (((tblCOMPANY_T EST.TEST_GROUP_ NAME)= " & Me.CONSORTIUM_N AME & ") AND ((tblEMPLOYEE.D OT_CLASS)= ""DOT"") " & _
"AND ((tblEMPLOYEE.I NACTIVE_DATE) Is Null)) " & _
"ORDER BY Rnd(tblEMPLOYEE .EMPLOYEE_ID)"

Set inp = New ADODB.Recordset
inp.CursorType = adOpenKeyset
inp.LockType = adLockOptimisti c



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

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

Set test = New ADODB.Recordset
test.CursorType = adOpenKeyset
test.LockType = adLockOptimisti c
test.Open "tblQRTLY_TESTI NG", cnn1
'New Declarations
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim InsertSQL As String
Dim cnCh5 As ADODB.Connectio n
Set cnCh5 = New ADODB.Connectio n
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.ActiveConne ction = 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_I D = inp!EMPLOYEE_ID
companyID = inp!COMPANY_ID
employeeID = inp!EMPLOYEE_ID
MsgBox test!EMPLOYEE_I D
test!TEST_GROUP = Me!TEST_GROUP
test!TEST_CATEG ORY = Me!TEST_CATEGOR Y
If Me!TEST_CATEGOR Y = "BAT" Then
test!TEST_REASO N = "Breath Alcohol"
Else
test!TEST_REASO N = "Random"
End If
test!QUARTER = Me!QUARTER
test!YEAR = Format(Now(), "YYYY")
test!SAMPLE_DAT E = Date

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

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

Set cmdCommand.Acti veConnection = cnCh5

cmdCommand.Comm andText = InsertSQL
cmdCommand.Exec ute

test.Update
inp.MoveNext

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

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



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

End Sub
Feb 25 '07 #1
3 2642
willakawill
1,646 Top Contributor
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
Bhavsan
2 New Member
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.Exec ute

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_I D = inp!EMPLOYEE_ID
companyID = inp!COMPANY_ID
employeeID = inp!EMPLOYEE_ID
MsgBox test!EMPLOYEE_I D
test!TEST_GROUP = Me!TEST_GROUP
test!TEST_CATEG ORY = Me!TEST_CATEGOR Y
If Me!TEST_CATEGOR Y = "BAT" Then
test!TEST_REASO N = "Breath Alcohol"
Else
test!TEST_REASO N = "Random"
End If
test!QUARTER = Me!QUARTER
test!YEAR = Format(Now(), "YYYY")
test!SAMPLE_DAT E = Date

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

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

Set cmdCommand.Acti veConnection = cnCh5

cmdCommand.Comm andText = InsertSQL
cmdCommand.Exec ute

test.Update
inp.MoveNext

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




inp.Close
test.Close
Set inp = Nothing
Set test = Nothing
Feb 25 '07 #3
willakawill
1,646 Top Contributor
The error suggests that tblRandom is not in DRUG_ALCOHOL_DA TA.MDB
Perhaps it is spelled slightly differently.
Feb 25 '07 #4

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

Similar topics

0
1878
by: Dan Perlman | last post by:
From: "Dan Perlman" <dan@dpci.NOSPAM.us> Subject: ODBC creating nulls? Date: Friday, July 09, 2004 10:43 AM Hi, Below is my VB6 code that writes data from an Access 2000 table to a PG table. The " & "" " on the right of each line should prevent nulls from being
3
3900
by: Bruno Luis | last post by:
Hello I'm using A97 and i'm having some trouble with creating a recordset with data from two different databases. I connect to a remote database and make this data the default source for my continuous form (Set me.recordset = rs). The problem is: one of the fields of the remote database is a foreign key (two digit number), and the table...
5
4320
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one child table (StudentProgress). The course progress records how a student progresses on a course. I have one course (History) and one student called...
2
1897
by: Chris via AccessMonster.com | last post by:
I have done something like this a million years ago my developer skills have diminished to nothing but now I am at a new job trying to regroup what I lost. This one came across my desk recently. I need to create tables based on grouped recordsets the additional challenge is that the table name would equal the value or variable of the grouped...
0
2555
by: billmiami2 | last post by:
Perhaps many of you MS Access fanatics already know this, but it seems that stored procedures and views are possible in Jet. I thought I would leave this message just in case it would help anyone. I discovered this the other day while doing some experiments with ADO and ADO.NET. Basically, I wanted to run a stored MS Access query with...
6
7611
by: F-13 | last post by:
I'm working on a BOM in Access 200 from an example downloaded from from the web. The sample database contains three tables, Assemblies (the list of items needed to assemble any assembly), Components (the list of items recognised by the Assemblies Table)and Output (a table used to display the BOM from a chosen assembly). It works fine but...
44
4506
by: Greg Strong | last post by:
Hello All, Is it better to create a query in DAO where a report has 4 sub-reports each of whose record source is a query created at runtime and everything is in 1 MDB file? From what I've read and experienced it appears DAO is the way to go in this situation, so when is it good to use ADOX to create queries? Why do I ask the question? ...
4
5479
by: andy.mcvicker | last post by:
Hi Gang I have a large VB program that at one point does a lookup to a small table (26 rows by 3 columns). With this table I have to do some counting and retrieval of data. I'm finding that this slows the program right down. Is there any way I can take a copy of the table in memory and access it there. Perhaps a cursor or something? ...
1
2262
idsanjeev
by: idsanjeev | last post by:
<%@ Language =vbscript%> <% Option Explicit %> <html> <head><TITLE>VOICE OF BARAUNIANS</TITLE></head> <body> <!--#include file="front.inc"--> <div style="Position:Absolute; width:700; TOP:0; left:180; background-color:#f0f0f0"> <% Dim conn dim objrs
0
7467
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7401
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7419
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7756
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5326
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4944
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1879
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1014
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
703
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.