Populate results from SQL Server using Recordset in MS Access using VBA code

Question

I am not sure how I can put the results from Record Set into a query or result pane in MS Access using VBA code. The results in the recordset are from SQL Server, so I want to display the results in MS Access. I need to do it this way, is that possible? I would think I need to do something where are x's are.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnString As String
 
strConnString = "Provider=SQLNCLI11;Server=SRV;Database=Staging;Trusted_Connection=yes;"

Set conn = New ADODB.Connection
conn.Open strConnString

Set rs = conn.Execute("Select * from MSAccess_APP_ComplianceDashBoard ")

XXXXXXXXXXXXXXXXXX

rs.Close

Does this answer your question? MS Access displaying vba select query in datasheet — Erik A
– Erik A, Commented Jul 6, 2020 at 15:59
display the results in MS Access - userform (datasheet mode) or report. — Scott Holtzman
– Scott Holtzman, Commented Jul 6, 2020 at 16:00
@ScottHoltzman Display results in grid screen I guess it is called datasheet. I am not looking to load it to text boxes or anything like that. It has hundred rows to show. — emanon
– emanon, Commented Jul 6, 2020 at 16:07
@ErikA The example that you sent to me uses the table or linked table in MS Access. The recordset that I have in my code example is from SQL Server. I am basically trying to bring in results and put it in the datasheet. — emanon
– emanon, Commented Jul 6, 2020 at 16:15
@emanon - load sql results to temp table and build a form in datasheet view from that temp table. or you can create view in sql server and link that view into Access (maybe, not sure if you can link view). but maybe create stored proc to create table in SQL and link that table into db and base form on that). — Scott Holtzman
– Scott Holtzman, Commented Jul 6, 2020 at 16:22

Albert D. Kallal · Accepted Answer · 2020-07-06 16:26:27Z

0

Assuming a continues form,or a multiple items form?

In the forms on-load event just go:

me.ReocrdSource = "Select * from MSAccess_APP_ComplianceDashBoard"

It is of course assumed that you have a linked table to SQL server with the above name.

In fact, you can set the forms record source in design mode and as a result you need zero lines of code.

So, setup a linked table - you not need to write any code.

answered Jul 6, 2020 at 16:26

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fabricio Fonseca · Accepted Answer · 2020-07-06 16:28:30Z

0

Create a pass-through query, put your connection string there, and do whatever you your needs over this query.

answered Jul 6, 2020 at 16:28

Fabricio Fonseca

1395 bronze badges

Collectives™ on Stack Overflow

Populate results from SQL Server using Recordset in MS Access using VBA code

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related