I'm trying to use SHAPE to get data into a MSHFlexGrid control on a form in a secured Access database but I'm falling at the first hurdle. I get a 3079 error "The connection cannot be used to perform this operation. It is either closed or invalid in this context" when I attempt to create an ADODB.Recordset like this:
Dim strConnection As String
Dim strSQLBand0 As String
Dim strSQLBand1 As String
'Get Data Provider.
strConnection = "Data Provider=Microsoft.Jet.OLEDB.4.0;"
'Get Data Source and System Database from existing connection.
strConnection = strConnection & "Data Source=" & .Properties("Data Source") & ";"
strConnection = strConnection & "Jet OLEDBystem database=" & .Properties("Jet OLEDBystem database") & ";"
'Open new connection object.
Set cnnShape = New ADODB.Connection
.Provider = "MSDataShape"
.Open strConnection, [UserID], [Password]
strSQLBand0 = "SELECT tblCustomer.strCustomer, tblCustomer.strCustomer_ID FROM tblCustomer;"
strSQLBand1 = "SELECT tblJob.lngWorks_Order, tblJob.strCustomer_ID FROM tblJob;"
Set rstCustomer = New ADODB.Recordset
rstCustomer.Open "SHAPE (" & strSQLBand0 & ") APPEND ((" & strSQLBand1 & ") RELATE strCustomer_ID TO strCustomer_ID) AS rsJob"