[RESOLVED] Select statement
I want to write a function to import my old database into a new database. The database consist of a number of tables (currently 14). In the function everything will work the same for each table except the select statement. This changes for each table. So there is a lot of code that can be reused on each table in the database.
Here is an example of one of my select statements (for Rst_New):
"SELECT * FROM TableName WHERE FieldName = '" & RstOld("FieldName") & "'"
So what I would like to achieve is to write the function that will do the job and then just pass the TableName and the select statement to the function for each table. The function will then open the recordset, import the data that is not yet in the table. The problem is that RstOld will only be opened in the function once it is called for each table in the database.
Is there a way to pass a select statement like the one above to the function, for instance as a string, and then let the function use that passed 'string' to open the needed recordset?
Hope I'm making sense!
[1105 byte] By [
Bezzie] at [2007-11-20 11:54:11]
# 1 Re: [RESOLVED] Select statement
Private Function ExecuteSelect(byVal TableName as string, ByVal FieldName as String, ByRef ConObj as ADODB.Connection) as RecordSet
Dim Rst as ADODB.RecordSet
Set Rst = New ADODB.Recordset
rst.Open CStr("SELECT * FROM " & TableName $ " WHERE FieldName = '" & RstOld("FieldName") & "'"), ConObj
return Rst
End Function
Something like this should work.
# 2 Re: [RESOLVED] Select statement
Make sure that you close everything that you set.
I pass a 9 digit trans which breaks down into two fields which are returned. I know that it will always find a transaction, so there is no default or error trapping needed.
Function GetTransType(xk)
Dim xkBD$, xkTD$
Dim adorec As Object
Set adorec = New ADODB.Recordset
xkBD = Left$(xk, 6)
xkTD = Right$(xk, 3)
strsql = "SELECT * "
strsql = strsql & "FROM Transactions "
strsql = strsql & " WHERE BatchDate = '" & xkBD & "'"
strsql = strsql & " AND TransNo = '" & xkTD & "'"
adorec.Open strsql, cnxn
If adorec.EOF = False Then
GetTransType = adorec.Fields(9)
End If
Set adorec = Nothing
End Function
rst(0) is the first field. You could pass field numbers and a table name
# 3 Re: [RESOLVED] Select statement
I want to build my select statement and then pass it to a second function which would then open the recordset.
TableA would have the following select statement:
mySQL = "SELECT * FROM Aviaries WHERE ANum = '" & _
OldRst("ANum") & "'"
TableB's could look like this:
mySQL = "SELECT * FROM BResults WHERE Year = '" & _
OldRst("Year") & "' and CRnumber = '" & _
OldRst("CRNumber") & "' and HRnumber = '" & _
OldRst("HRNumber") & "'"
And TableC's would be something like this:
mySQL = "SELECT * FROM Expenses WHERE ECategory = '" & _
OldRst("ECategory") & "' and EDate = #" & _
OldRst("EDate") & "# and EDescription = '" & _
OldRst("EDescription") & "' and EAmount = " & _
OldRst("EAmount")
etc. etc. Some of my select statements is pretty involved. If I could for instance have an array (ParamArray) with the following values:
TableA, Select statement for TableA as above
TableB, Select statement for TableB as above
TableC, Select statement for TableC as above
For iCounter = 0 to Ubound(ParamArray)
Table_Import(ParamArray(iCounter,1), ParamArray(iCounter,2)
Next iCounter
Sub Table_Import(ByVal TheTable as String, ByVal TheSelectStatement as String)
Dim OldRst as New ABODB.Recordset
Dim NewRst as New ADODB.Recordset
Dim mySQL as String
mySQL = SELECT * FROM " & TheTable
OldRst.Open mySQL, OldDbs
Do While not OldRst.EOF()
NewRst.Open TheSelectStatement, NewDbs
'from here on to the end of the Sub the code is exactly the same for
'each and every table in the database
'all the rest of the code to import the fields for each record if it doesn't exist
'and default the fields if they don't have values etc. etc.
NewRst.MoveNext
Loop
End Sub
Currently I have to make a function for each table with a few lines of code that differs and the rest exactly the same.
That's a lot of duplicated lines of code which I'm trying to avoid.
Bezzie at 2007-11-9 19:34:06 >
# 4 Re: [RESOLVED] Select statement
Sub Table_Import(ByVal TheTable As String, ByVal TheSelectStatement As String)
Dim OldRst As New ABODB.Recordset
Dim NewRst As ADODB.Recordset
Dim mySQL As String
mySQL = "SELECT * FROM " & TheTable
OldRst.Open mySQL, OldDbs
Do While Not OldRst.EOF()
NewRst.Open TheSelectStatement, NewDbs
'Just call another function here which will execute the rest of the statement
'which are common for all tables
NewRst.MoveNext
Loop
End Sub
# 5 Re: [RESOLVED] Select statement
Still wouldn't solve the problem of passing the SELECT statement as an parameter to the function.
Bezzie at 2007-11-9 19:36:11 >
# 6 Re: [RESOLVED] Select statement
I am not very much clear with your previous posts what exactly you want.
Can you explain exactly what are you looking for?
# 7 Re: [RESOLVED] Select statement
I wanted to create a SELECT statement and then pass it to a function that will use that SELECT statement to open a recordset. Problem is that part of that SELECT statement will contain data that will only be available in the function that will receive the SELECT statement as a parameter.
But not to worry. Found a workaround, not what I wanted, but still saved me a few hundred lines of code duplication.
Thanks guys.
Bezzie at 2007-11-9 19:38:07 >
