I need some advice on how I can pull in all rows from SQL Server Table without linked table in MS Access? The reason why we cannot use link table is because the table name will change and creating the linked table on the fly will not work due to DLL problem. I would think we can modify the below code to pull in all results directly from SQL Server. Right now, I have a linked table with my userName. So, it works fine for me, but not for others who will need to use it.
Code:
' Load to query result
strSQL = "Select * from RPT_MSAccess_APP_" + UserName + "_ComplianceDashBoard "
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryResult")
qdf.SQL = strSQL
DoCmd.OpenQuery ("qryResult")
Set qdf = Nothing