Help with connection string converting from .ADP to .ACCDB

[cleteh]

I highlighted the parts in red that I believe need converted


Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSql As String
Dim i As Integer
Dim j As Integer
Dim s As String
Dim s2 As String
Dim sQuestion(2000) As String

Set db = New ADODB.Connection
db.ConnectionString = "PROVIDER=SQLNCLI10;SERVER=SVBKSA69901M2W\IKM_QA;DATABASE=RiskControl;TRUSTE D_CONNECTION=yes;"
db.Open

Set rs = New ADODB.Recordset

[HiTechCoach]

Are wanting to convert to DAO?

See: ADO Programming Code Examples
(http://hitechcoach.com/microsoft-office/access/access-links?task=weblink.go&id=585)


This page is a reference for developers, demonstrating how to use the ADO library to list and manipulate the objects in Access.

[cleteh]

Yes I need to convert to DAO, right now im getting an error on the line in red below on db.connect. It says Can't assign to read-only property.

Dim db As DAO.Connection
Dim rs As DAO.Recordset
Dim sSql As String
Dim i As Integer
Dim j As Integer
Dim s As String
Dim s2 As String
Dim sQuestion(2000) As String


Set db = CurrentDb
db.Connect = "PROVIDER=SQLNCLI10;SERVER=SVBKSA69901M2W\IKM_QA;DATABASE=RiskControl;TRUSTE D_CONNECTION=yes;"
db.Open

Set rs = New ADODB.Recordset
sSql = "SELECT * FROM Auditor WHERE UserName = '" & GetUserName & "'"
rs.Open sSql, db, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
m_iUserID = rs.Fields("ID")
m_sUserName = rs.Fields("UserName")
lblUser.Caption = rs.Fields("Name")
End If

rs.Close

[HiTechCoach]

Your code is a mix of ADO and DAO.

If you can create a linked table then the DAO code is simple.

AFAIK, this will only work with linked tables:


Set db = CurrentDb

The above code is setting a database connection to the opened Access databases. This is where it woudl look for the linked tbales. This is NOT the SQL Server database.


If you can't use a linked table then then this may help:

Connection.Database Property (DAO) (https://msdn.microsoft.com/en-us/library/office/ff834675.aspx)