Populate MS Access form from SQL Server table. (1 Viewer)

ordnance1

New member
Local time
Today, 10:10
Joined
Oct 12, 2018
Messages
1
I am on a mission to populate a MS Access form with data from an SQL Server table. I do not want the SQL Server table to be linked in Access. Doing my Google searching the closes I have gotten is the code below. The only problem is that it requires the SQL Server table to be linked to Access. I know my connection string:

"DRIVER=SQL Server Native Client 11.0;SERVER=65.999.228.253;UID=sa;PWD=123456aA@;DATABASE=Auxiliary_3242_Secretary;" (I Changed password and server in this example).

Can the code below be modified in any way to achieve my goal?

Code:
    DoCmd.OpenForm "frm_CashBook_Transaction_Testing"
    Set RstCashbook = New ADODB.Recordset
    RstCashbook.CursorLocation = adUseClient
    RstCashbook.Open "Select * From dbo_vw_Bank_RunningTotal_AllAccounts WHERE Cashbook_Date_ID = " & Nz(TempVars!CashBook_Date_ID, 0) & " Order By Bank_Account_ID, Cashbook_Date_ID, CheckNumber_Numeric, Payee_Payor, Description", _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Set Forms("frm_CashBook_Transaction_Testing").Recordset = RstCashbook
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 19, 2013
Messages
14,694
once you have connected and populated your ADO recordset, you can disconnect it - see this link for one of many examples

presume you are aware that once disconnected you have a pretty big job on if the user is making changes then needs to update sql server?

Not only will this have to be on a record by record basis but you will also need to check if the record to be updated has been modified by someone else or even deleted and then manage the situation accordingly.

Further - access forms standard functionality for filtering and sorting will not work with ADO recordsets, you will need to write your own substitutes
 
Last edited:

isladogs

CID VIP
Local time
Today, 18:10
Joined
Jan 14, 2017
Messages
16,388
The connection string info in post #1 is also given in the first link you provided ...where several suggestions have been made
 

sonic8

AWF VIP
Local time
Today, 19:10
Joined
Oct 27, 2015
Messages
612
Can the code below be modified in any way to achieve my goal?
Yes. Instead of using CurrentProject.Connection create your own ADODB.Connection with the direct connection string to the server. There are countless examples for this on the web, I don't think I need to provide more details.
 

Users who are viewing this thread

Top Bottom