ADO connection, how do?

[clebergyn]

Hello, friends, I have a form with a subform, I am new to ADO, made a Via ADO connection in form and subformularo with fields "master" and "child" after connect you can not register / change data, nor work ties "LinkMasterFields" and "LinkChildFields" the subform, does not filter. What to do?


the code I use is this:

MyDatabase = "\\RECEPCAO\Sistema\Tabelas-sistema.accdb "
Dim mOrigem
mOrigem = "Select * from [tab medico] where [codigo] like " & Forms![medico entrada]![Medico]
cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDatabase
rs1.CursorLocation = adUseClient
rs1.Open mOrigem, cn1, adOpenDynamic, adLockOptimistic
Set Forms("medico atendimento").Recordset = rs1
rs1.Close
cn1.Close
Set rs1 = Nothing
Set cn1 = Nothing

want to make the ADO connection and use the formuário and subform similar to DAO, no way?

[June7]

Why are you using VBA to set connection? What is the backend db? Why not set up linked tables? I've never set a RecordSource with a recordset object. Review http://stackoverflow.com/questions/6...as-a-recordset which indicates cannot be done. Does it work with DAO?

[clebergyn]

my bd is already divided into back-end and front-end. I want to ADO connection, to improve the simultaneous accesses and improve performance. With back-end and front-end I will have how many simultaneous access?

[June7]

I don't routinely use VBA to set connection to backend. I use table links. Not sure what you mean by 'simultaneous accesses'.

[CJ_London]

linkchild and linkmaster only works with dao recordsets

So your subform recordset needs to have the criteria to limit the recordset to what you want

filtering is also different with ADO

you cannot filter on LIKE '*abc*', only LIKE 'abc*'
you assign the filter to the recordset rather than the form (me.recordset.filter=....) rather than me.filter=...
and you need to reassign the recordset (set me.recordset=me.recordset) to activate the filter rather than using filteron=true

other things you cannot do with ADO is join tables from different databases

[clebergyn]

on "simultaneous access" I meant "multiplayer". several users access the db at the same time. I do not know how to speak English

[June7]

I have multiple users and I use linked tables. Each user has their own copy of frontend installed on their local C drive. I also use UNC pathing for the table links.

[clebergyn]

What is UNC "path is in the table links"? what is it for? improves performance, and improves for a bd multiple users?

[June7]

UNC - Universal Naming Convention. Instead of mapped drive path in table links, such as R:\path\backend.accdb, uses server name in the path: \\servername\path\backend.accdb

This means users are not required to have a specific drive mapping for the frontend to link to backend.

I use VBA code to set this UNC link.

[clebergyn]

I made the links of the network tables, network patch. Well, then tell me. I have a database (Access-using DAO) with tables that are linked by the network, how many hits multiple users I'll be able to have on my system? here in Brazil some speak 10 multiple-users and others say it's more. Do you know?

[June7]

Review http://stackoverflow.com/questions/9...s-file-support

[clebergyn]

well, like talking about SQL Server. But then you know to SQL server, I using the tables by the server, I'll have to change the codes of my front-and program (Access)? I will be able to use the view query, use the forms linked tables? use bd Access with DAO?