Hello, everyone. Please bear in mind that I have a very basic knowledge of VBA, but I think I can understand logic.
I'm looking for a way to build a module that can help me parse the data in one massive table, using "control tables" to determine the valid value set for the fields I need to consider in the selection. I cannibalised a piece of code from a similar post that I think will help me, but need your guidance, by explaining what each of the commands actually do, so I can try it out in my database. I think that I can figure out the rest.
' START CODE <---------------------
Sub loopFiles()
Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic ' Comment - suposse I can replace "Schools" with any table in my current DB....
MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic ' Comment - What is this?!?
Dim schNbr
Dim mySQL As String
MyRecSet.MoveFirst
While Not MyRecSet.EOF ' While loop that goes through RecordSet for execution - I get this
schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "'" ' This I can figure out
Debug.Print mySQL ' debug statement
'DoCmd.RunSQL mySQL, 0 ' for actual execution
MyRecSet.MoveNext
Wend
MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing
End Sub
' END CODE <---------------------
I wish I could import the data into an Oracle DB and write an SQR for this, but can't, since the team has been using this DB (and plan to use it for years to come).
Thank you very much in advance!
D
2 2622 NeoPa 32,556
Expert Mod 16PB
MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic ' Comment - What is this?!?
Open Method (ADO Recordset)
Opens a cursor.
Syntax
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
I 'm not sure exactly what you're after here but if you look in the help system for things you should get fuller info.
I can't see how this line can come before any code that sets up mySQL - a string containing SQL code.
Looks like an 'alternative' bit of code - doesn't go here nor can be run exactly as is, but an example of another approach.
If used, should have extra code in body processing through this recordset.
3 lines before - MyRecSet.ActiveConnection = myConn
Should probably have a 'Set' before it as it's an object assignment.
Any questions let me know ... -
- ' as you are putting this in a module I would recommend using Function rather than sub as it is easier to call
-
Function loopFiles()
-
- ' create a variable to hold connection to database
-
Dim myConn As ADODB.Connection
- ' set that connection to current database
-
Set myConn = CurrentProject.Connection
-
- 'create a variable to hold recordset in database (table or query)
-
Dim MyRecSet As New ADODB.Recordset
-
- 'reference the current database as location of recordset
-
MyRecSet.ActiveConnection = myConn
- 'open the recordset, in this case presumably the Schools table
-
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic
-
- ' Comment - suposse I can replace "Schools" with any table in my current DB....Answer yes, any table or saved query or sql statement
-
- 'mySQL refers to a previously defined sql statement
- 'Look for 'Dim mySQL As String' - defines string variable to hold sql statement
- 'And mySQL="SELECT <Fields> FROM <TableName> WHERE <Criteria>" - Query definition
-
-
MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic ' Comment - What is this?!? - This then opens the query as a recordset
-
-
Dim schNbr ' defaults to a variant which essentially can hold any type of value
-
Dim mySQL As String ' see above
-
- ' this moves to first record in the recordset
-
MyRecSet.MoveFirst
-
-
While Not MyRecSet.EOF ' While loop that goes through RecordSet for execution - I get this (until it reaches end of file)
-
- 'Looks for a field in the recordset called "No" and finds its value in the current record then passes it to schNbr variable
-
schNbr = MyRecSet.Fields("No").Value
-
mySQL = "select * from schools where no = '" & schNbr & "'" ' This I can figure out (See above for opening it as a recordset)
-
-
Debug.Print mySQL ' debug statement
-
'DoCmd.RunSQL mySQL, 0 ' for actual execution (won't work for a SELECT statement used for UPDATE, DELETE and INSERT statements mostly - i.e. Action queries)
-
-
MyRecSet.MoveNext ' move to next record
-
Wend ' loop unless end of file
-
-
MyRecSet.Close ' all recordsets must be closed
-
Set MyRecSet = Nothing ' and then variable reset to nothing
-
Set myConn = Nothing ' don't close database but must reset variable to nothing
-
-
End Function
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dave |
last post by:
Hello there,
I am at my wit's end ! I have used the following script succesfully to
upload an image to my web space. But what I really want to be able to do is
to update an existing record in a...
|
by: .:mmac:. |
last post by:
I have a bunch of files (Playlist files for media player) and I am trying to
create an automatically generated web page that includes the last 20 or 30
of these files. The files are created every...
|
by: M Wells |
last post by:
Hi All,
I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'
To explain our situation:
...
|
by: Randy Yates |
last post by:
I'm a complete newbie to postgres so please look the
other way if these questions are really stupid.
Is it legitimate to have one database per data file? For
organizational and backup purposes,...
|
by: rAinDeEr |
last post by:
Hi,
I have a web application with a table to store terms and
conditions of a Company.
This may some times run into many pages and some times it may be just a
few sentences. It is a character...
|
by: Chris |
last post by:
I have a meetings section I'm developing on our intranet. Using PHP/MySQL.
Meeting info and Meeting docs reside on 2 related tables in the db. Users
may want to upload anywhere from 1 to 10 or...
|
by: Christoph Haas |
last post by:
Hi, list...
I have written an application in Perl some time ago (I was young and needed
the money) that parses multiple large text files containing nested data
structures and allows the user to...
|
by: Robert Neville |
last post by:
Basically, I want to create a table in html, xml, or xslt; with any
number of regular expressions; a script (Perl or Python) which reads
each table row (regex and replacement); and performs the...
|
by: Chris Carlen |
last post by:
Hi:
Having completed enough serial driver code for a TMS320F2812
microcontroller to talk to a terminal, I am now trying different
approaches to command interpretation.
I have a very simple...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |