February 12, 2019 at 8:31 am
I am working with Excel and MS SQL 2008.
I defined a virtual (unbound) recordset based on data from Excel named rstADO. I tested it, I did populate it and it works.
Dim rstADO As ADODB.Recordset
Dim fld As ADODB.Field
Set rstADO = New ADODB.Recordset
With rstADO
.Fields.Append "Job", adVarChar, 8, adFldKeyColumn
.Fields.Append "DateCurr", adDate, 7, adFldMayBeNull
.Fields.Append "DateSyspro", adDate, 7, adFldMayBeNull
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockPessimistic
.Open
End With
I must update another SQL-based (bound) recorset with the first one. For now I am just doing a SELECT query and I have syntax/procedure problems that I can't solve.
I thought something like
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=XXX;Trusted_Connection=No;DATABASE=XXX..."
cnn.Open
rst.ActiveConnection = cnn
rst.CursorLocation = adUseServer
rst.Open "SELECT W.Job, W.JobDeliveryDate, A.DateCurr FROM dbo.WipMaster W INNER JOIN " & rstADO & " A ON A.Job=W.Job"
I get a "Compîle Error : Type Mismatch", highlightling the word rstADO
What would the proper way to get my SELECT query working?
February 12, 2019 at 8:54 am
saintor1 - Tuesday, February 12, 2019 8:31 AMI am working with Excel and MS SQL 2008.I defined a virtual (unbound) recordset based on data from Excel named rstADO. I tested it, I did populate it and it works.
Dim rstADO As ADODB.Recordset
Dim fld As ADODB.Field
Set rstADO = New ADODB.Recordset
With rstADO
.Fields.Append "Job", adVarChar, 8, adFldKeyColumn
.Fields.Append "DateCurr", adDate, 7, adFldMayBeNull
.Fields.Append "DateSyspro", adDate, 7, adFldMayBeNull
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockPessimistic
.Open
End WithI must update another SQL-based (bound) recorset with the first one. For now I am just doing a SELECT query and I have syntax/procedure problems that I can't solve.
I thought something like
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=XXX;Trusted_Connection=No;DATABASE=XXX..."cnn.Open
rst.ActiveConnection = cnn
rst.CursorLocation = adUseServerrst.Open "SELECT W.Job, W.JobDeliveryDate, A.DateCurr FROM dbo.WipMaster W INNER JOIN " & rstADO & " A ON A.Job=W.Job"
I get a "Compîle Error : Type Mismatch", highlightling the word rstADO
What would the proper way to get my SELECT query working?
You are getting a compile error because rstADO is not a string, it's a recordset.
If you want to construct some SQL that will run you would need to iterate through all the rows in rstADO to make it a string that would be valid bit of SQL. So you'd need to create a string like this from rstrADO:
(VALUES
('Job01','20190131'),
('Job02','20190131'),
('Job03','20190131')) AS A(Job,CurrDate)
Then where you have rstADO in your string you can replace it with the string that contains the new string you have created from iterating through the recordset. Your final bit of SQL would then look something l
rst.Open "SELECT W.Job, W.JobDeliveryDate, A.DateCurr FROM dbo.WipMaster W INNER JOIN " &
"(VALUES
('Job01','20190131'),
('Job02','20190131'),
('Job03','20190131')) AS A(Job,CurrDate) " & " ON A.Job=W.Job"
February 12, 2019 at 2:44 pm
Unfortunately there would be hundreds of values.
I tried to reference to rstADO as a regular recordset (not a string), as it was defined earlier in the code. Didn't work either.
rst.Open "SELECT W.Job, W.JobDeliveryDate, A.DateCurr FROM dbo.WipMaster W INNER JOIN rstADO A ON A.Job=W.Job"
February 13, 2019 at 5:13 am
saintor1 - Tuesday, February 12, 2019 2:44 PMUnfortunately there would be hundreds of values.I tried to reference to rstADO as a regular recordset (not a string), as it was defined earlier in the code. Didn't work either.
rst.Open "SELECT W.Job, W.JobDeliveryDate, A.DateCurr FROM dbo.WipMaster W INNER JOIN rstADO A ON A.Job=W.Job"
I've never heard of using unbound recordset as part of a SQL query. What makes you think you can do this?
If there are too many rows in the recordset, you could insert them into a temporary table in SQL Server then use that table to do the join.
February 13, 2019 at 9:37 am
Well I just did that - I used a temporary SQL table. Maybe not the most sophisticated query but it works. I thought that I could handle a temporary recordset the same way as one coming from a SQL table, but apparently not.
February 13, 2019 at 11:13 am
saintor1 - Wednesday, February 13, 2019 9:37 AMWell I just did that - I used a temporary SQL table. Maybe not the most sophisticated query but it works. I thought that I could handle a temporary recordset the same way as one coming from a SQL table, but apparently not.
Definitely not. The only way to do something like that is to handle the entire operation in SQL, but if the connections are to different servers, that would be a problem. Since the temp table worked, I can safely assume it's the same server, so why not write a query that derives the set you brought in as a recordset as a CTE instead, within the SQL, and join to that CTE. Then you may not need to bring the rstADO recordset in at all.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply