I'm running MS Access 2016 connecting to a linked table on SQL Server (2012, I think, if it matters), in order to allow a single user (me) to quickly manipulate data in that table. I have a fairly intensive pass-through query which creates a list of primary key values representing rows where there's something wrong with the data in the table (usually just nulls where there shouldn't be nulls, but also invalid combinations of values and invalid dates).
I would like to display only the rows that are identified by my pass-through query, so I can quickly input missing values and make other corrections. However, I am at a complete loss as to how to do that.
In an attempt to sort-of follow best practices, I tried to make the relevant rows display in a form where only specific fields would be editable. However, MS Access keeps throwing an error about how a relationship isn't defined between the queries and the table, even though I set up the relationships.
Failing that, I tried to make an editable query using the relevant queries and table, but none of the queries I've made have had editable recordsets, for reasons I don't really understand. As far as I can tell, the relationships between the queries and the table has been one-to-one, and the linked table is normally editable directly in MS Access. Even when I ditch the additional info query and just join the linked table to my error-finding passthrough query, I can't create an editable recordset.
Is there a good way to accomplish my goals? I'm starting to feel like my best option here is to create some sort of temporary table in Access to store the values I'm editing, and then merge them into the linked table, but that seems kind of clunky to me. Do I have any other options?
Ok, perhaps you have the stored procedure after finding those bad rows (or whatever reason), you have it return that list of keys.
The next question then becomes?
Well, if you only ever returning say < 100 rows, then I think that just returning a string of PK values would work fine.
So, the stored procedure can either:
Return one string to access with pk values, say like
34,3443,3,55333
(in the final statement of your stored procedure, just do a select on the string and Access will see the one row, one column as a recordset).
So, your code would be something like:
Sub EditBad()
Dim strKeys As String ' list of keys from stored procedure
Dim rstKeys As DAO.Recordset
With CurrentDb.QueryDefs("MyStoredProc")
.SQL = "exec sp_myproc " & "some possible parameters you send to stored procedure"
Set rstKeys = .OpenRecordset
End With
strKeys = rstKeys(0)
rstKeys.Close
' now launch our edit form based on the keys returned
Dim strWhere As String
strWhere = "ID IN (" & strKeys & ")"
DoCmd.OpenForm "frmEditBadGuys", , , strWhere
End Sub
Now, if the list to be returned of bad keys is going to larger? Well, then from the stored procedure, simply return the values as a table. So, in place of a single select on the "string of keys" from the stored procedure, you return a select of the bad key values you want to check.
So, now we have:
With CurrentDb.QueryDefs("MyStoredProc")
.SQL = "exec sp_myproc " & "some possible parameters you send to stored procedure"
Set rstKeys = .OpenRecordset
End With
strKeys = ""
do while rstKeys.EOF = False
if strKeys <> "" then strKeys = strKeys & ","
strKeys = strKyes & rstKeys(0)
.movenext
loop
rstKeys.Close
strWhere = "ID IN (" & strKeys & ")"
DoCmd.OpenForm "frmEditBadGuys", , , strWhere
Again, I think this solution is good for say 100-200 rows.
If the results returned are say 1000-2000 records? Well then that "in (value1, value2) etc. string gets too long, runs too slow, and will blow up - you limited to I think about 4000 characters - but such a where clause in SQL is simply too long aleady, and it going to run turtle slow.
So, in place of procesisng the returned recordset into a long string?
Send the results to a local temp table. You can then created a editable form, and join on the local table to the linked table. In this case, make sure the base table is the linked table, and you may well have to do a left join to the local temp table (an inner might work, but left should work.
And if performance is really required? Well, send the data sql server side to a temp table (now, by temp table, I don't mean an actual sql server temp table, since you can't use those client side in Access. (what setup you use would depend on how multi-user this applcaiton has to be).
So, you could make a sql server view (that does this join). Keep in mind that sql server views ARE editable from client side Access (unlike pass-though query - they are read only for the most part).
So, you could also have a "user" column in that "sort of temp" table sql side, and you not only add the PK values, but also a user column name. And then in access client side? You launch the "bad guys/bad data" edit form and use a where clause based on user name (this would allow mutli-user setup). And this setup would by far perform the best.
So, get the stored procedure to spit back either:
A one column select from the stored procedure that has the keys, and try the "in clause" as a where clause against a client side form that you know works (can edit data). However, I seem to recall that the access client does not do a great job with "in clause" for SQL Server - you have to give this idea a try.
The local table of PK values also would work well - not much more code, and this would be based on a linked table join + a left join to the local table of PK values that you wrote out. If the linked table is not too large, then this can work (but again, performance issues will crop up).
So, ultimate performance would be a server side view and it being joined to the temp working table of bad PK values you want to work on. And for multi-user, then you have to add a "user" column or some means to allow more then one user. Keep in mind that a "where" clause from access client works VERY well against a view. (but not those where "in (1,2,3) types of where clauses!
So, 3 possible roads above to try.