Update subform recordsource with ADO recordset

[Hudas]

I have a table linked to a backend database that is the recordsource of a subform. The problem is latency issue because of multiple users. I want to use ADO to update the record in the subform instead of linking it. I just want to get your opinion which is the best option... I have think of two at the moment. Since we can't use ADO as recordsource of the subform,

1. my plan is when the user opens the file a table will be created and will be updated by the recordset with all data specific to him which will be the recordsource of the subform. SO each user will equal to one table but that table will be deleted when he closes the database

2. I will have one single table that will be udpated with the users data and will just use a query as a recordsource of the subform. the data will be deleted when the user closes the file.

Additional option is very much appreciated

 

[MarkK]

Since we can't use ADO as recordsource of the subform

Perhaps you can -> https://msdn.microsoft.com/en-us/library/office/ff835419.aspx

 

[Hudas]

Hello MarkK,

Thank you for taking the time to read through my post and aswering it.

I tried the reference you gave me but the result on my subform are all #Name? and it seems its for MS Access 2013 and for later versions only. Im using MS Access 2010.

Any more ideas?

 

[jdraw]

I have no experience with ADO and subform set up you described, but a little googling found these that may mean more to you than me. If you have already seen them--ignore as appropriate.

Info from Juan Soto
Related info in a stackOverflow post
Earlier post on similar topic <--likely not helpful


Good luck.

 

[Hudas]

Thank you jdraw!

It seems to me that using ADO recordset as a recordsource requires more work than I imagined and based on your suggested link I have encoutered several items I'm not sure of how and when to use. I'm going to go back with my original plan that is update the table in my front end using ADO then use the table as recordsource for the subform. I'm going back to my original question, which of the two is better.

1. when the user opens the file a table will be created and will be updated by the ADO recordset with all data specific to him which will be the recordsource of the subform. SO each user will equal to one table but that table will be deleted when he closes the database

2. I will have one single table that will be udpated with the users data and will just use a query as a recordsource of the subform. the data will be deleted when the user closes the file.

Thank you!!!!