Using Access Recordset as SQL Variable

Squizzla

New member
Local time
Today, 00:39
Joined
Jan 13, 2014
Messages
5
Hello all.
This is my first ever post on an Access forum, so please be gentle (and feel free to move this is I’ve put it in the wrong place).

I am currently building (despite my limited previous Access experience) a customer reporting database. I’m having issues with one VBA macro which no amount of googling has provided a suitable answer.

I am trying to create a process which selects a customer’s site identifier and uses that to query an Oracle database through an ADODB connection. Which is all well and good until that customer has more than one site.

Here is a simplified version of what I have created:

Code:
'Get site details from current database on basis of company selected on form 
 
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim RsSites As New ADODB.Recordset
RsSites.ActiveConnection = cnn   
 
Dim MySQL as String
 
MySQL = "SELECT tblCompanyDetails.SiteID "
MySQL = MySQL & " FROM tblCompanyDetails "
MySQL = MySQL & " WHERE tblCompanyDetails.CompanyID = '" & [Forms]![frmAdviceNotes]![cboCompany] & "'
 
RsSites.Open MySQL
 
'Get Data from Oracle using SiteIDs 
 
Dim SON As New ADODB.Connection
Set SON = New ADODB.Connection
SON.Open "DRIVER={Microsoft ODBC for Oracle};UID=USer;PWD=Password;SERVER=Server;"
 
Dim RsData As New ADODB.Recordset
RsData.ActiveConnection = SON
 
Dim Site As String
Dim StartDate As String
Dim EndDate As String
 
Site = RsSites!SiteID
StartDate = [Forms]![frmAdviceNotes]![cboStart]
EndDate = [Forms]![frmAdviceNotes]![cboEnd]
 
Dim DataSQL As String
 
DataSQL = "SELECT Date, SiteID, Volume "
DataSQL = DataSQL & " FROM T_METERED_VOLUMES "
DataSQL = DataSQL & " WHERE SiteID IN ('" & Site & "') " 
DataSQL = DataSQL & " AND Date BETWEEN '" & StartDate & "' and '" & EndDate & "' "
 
RsData.Open DataSQL
 
'RsData then gets pasted into an Excel file which forms the report

As I said, this works perfectly when the customer only has one site, but I have failed to find a way to turn a recordset with multiple values into a SQL variable that can be used in the IN clause.

I have attempted to convert the recordset to a string using GetString, but could not find a way to correctly seperate out the records.

Does anyone has any ideas on how I might get this working? If a solution can be found, it would result in my eternal gratitude.

Many thanks.
 
Random stuff
1) Declare your variables in one "piece" always at the top of your Sub, not inbetween your code, this is a 'good practice' thing
2) Startdate and Endate are strings? or dates?
3) Date guess not as bad if it an oracle table
4) is siteid a number or a text?
either way change:
Code:
DataSQL = DataSQL & " WHERE SiteID IN ('" & Site & "') " 
to
DataSQL = DataSQL & " WHERE SiteID IN (" & Site & ") "
If a number, enter for Site: 1,203,3099,4,55876,etc
if a text, enter for site: '1', 'Amsterdam', 'Bruxel', 'etc'

That should do your trick for you :)
 
1) OK, thanks - I'll start doing that from now on
2) They have to be strings to work against the Oracle database - I've tried passing actual dates to it before, and that failed
4) The SiteID is a 13-digit number stored as text, so again it needs to be a string.

So are you suggesting that rather than storing the SiteIDs in the Access table as

1300000000000
1300000000001
1300000000002

They are put in like

'1300000000000'
'1300000000001'
'1300000000002'

?

That definitely wouldn't be my preferred solution as it would require changing a lot of other things elsewhere. But if there is no other way, I suppose it might have to do...

Thanks
 
2) no they dont have to be strings to pass to oracle, particularly if your date field is indeed a date field. If it is a proper date, you are best of using the oracle TO_DATE function to force oracle to use dates and not cause havoc on your indexes (if any) and cause potential problems.

4) No is not what I am suggesting, sorry didnt realize the SiteID was comming from a recordset
you will want to do this:
Code:
Site = "'" & RsSites!SiteID & "'"
rsSites.movenext
Do while not rssites.eof
    Site = ",'" & RsSites!SiteID & "'"
    rssites.mvoenext
loop
to build your Site variable to suite your IN clause in the SQL...
Then still change your in clause in the SQL to:
Code:
DataSQL = DataSQL & " WHERE SiteID IN (" & Site & ") "
Since the earlier code already takes care of that

** Disclaimer ** , all is aircode, writen on the forum and untested, but I hope you get the idea and can get it working if it fails. Plus I am more/only used to working with DAO, so it may need some adjusting here or there.
 
Boom! Jackpot!

It needed on little tweak as follows:

Code:
Site = "'" & RsSites!SiteID & "'"
rsSites.movenext
Do while not rssites.eof
    Site = [B]Site &[/B] ",'" & RsSites!SiteID & "'"
    rssites.movenext
loop

But it now appears to be working perfectly. Amazing - thanks!
 
Obviously the result of to quick copy/paste and not testing it but yeah, glad you got it fixed...
 

Users who are viewing this thread

Back
Top Bottom