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:
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.
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.