External RecordSource As SQL

[TKnight]

Hi, I have a database that needs bank account details adding with some degree of security. I took over the database from someone else and can't use the security wizard to implement proper security so i'm trying to create a workable solution. I've created a table to house the details in another DB which i've protected with the simple password security. I've also created a form in the front end where i want the details displayed. I have different user levels so I can restrict access to the button that opens the form.

I found some code on here about opening a password protected DB and modified it to try and set the recordsource of the form to the external table but I can't get it to work. I'm getting a type mismatch error because I don't think I can pass frmRST as the forms RecordSource. Can anyone fix my code or tell me a better way of doing it,

thanks, Tom.

Private Sub Form_Load()
Dim wrk As Workspace
Dim dbProtected As Database
Dim frmRST As Recordset

Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase("H:\Tom Knight\AMacctDets.mdb", False, False, ";PWD=am")
Set frmRST = dbProtected.OpenRecordset("SELECT * FROM [tbl_AppAcctDets];")

Me.RecordSource = frmRST

End Sub

 

[namliam]

I dont think you can send a recordset to a form like that.... Recordsource needs to be an SQL statement or table...

Also you should use dim ... as DAO.Recordset or dim ... as ADO.Recordset (disambiguate ...)

Greetz

 

[TKnight]

Thanks mate, just realised I don't have to set the external table as the recordsource, I can keep the fields unbound and use;
Me![AcctNumber] = frmRST![AcctNumber]

to populate them.

Thanks again, Tom.

 

[Dan_T]

Yeah keep the field un bound. Did you say you're using SQL Server? Sorry haven't the time to properly read your question.

If so this is what I generally do. Use a stored procedure (pre compiled code on the server) and pass a value to it that finds the record you want. Return just that record and populate the forms unbound controls. I've also used unbound text box to type the search info (Eg name) and if more than one record returned have populated a list box in a pop up form to select the record. The list box set to Value List and build a string from the search results. Select the record you want and redo the search.

This way you can use a number of stored procedures depending on what you type in the search box (ID field or text) You get the idea?

 

[TKnight]

Yeah thanks, i'm not using SQL but it's working well. I've found some stuff on finding records and adding to recordsets so i've managed to add/edit records back in the secure file, as well as view the info in the form.

All is quiet.... For now!!

Thanks, Tom

 

[namliam]

But use me. instead of me!

Search the forum there is an entire thread out there about the how when why what of this.

Greetz