POPULATING OR FILLING TEXT BOXES WITH DATA FROM A DATABASE 1

[TNN]

I can fill a db grid's fields with data from a database using a data control and writing SQL for the Recordsource property. Load time is relatively fast considering the number of fields that I can fill.

Is there a way to write SQL and fill text boxes with data from a database without using the recordset methods which are too slow.

I have 199 text boxes to fill with database data and using the recordset methods it takes approx 8 to 11 seconds to load all 199 text boxes.

Any help or suggestions appreciated.

TNN, TOM
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[ghubbell]

Hi TNN,
If there is anyway to save these as queries (or a query), then base say, a listbox or combo box on one the saved queries (or the whole form on one). Not sure what you're doing here but you could "fire" the query in the on load event, or at the click of a button, or save it outright as the recordsource. You will see a dramatic speed increase. This also makes maintenance a whole bunch easier! ;-) Gord
ghubbell@total.net

 

[TNN]

Gord,
I'm having trouble understanding what you are saying.
Right now I am using an access databse and accessing the database using ADO by way of a class module. The SQL is executed by ADO. I then am using the created recordset, created by ADO, to move thru the recordset and post to the text boxes.

Can you please expand on what you are saying.

Thank You
TNN, TOM
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[ghubbell]

Hi TNN,
Let me try and we'll see if we're on the same channel:
If you're just after some select data from a table or tables in the database and you want to populate some fields on a form or push values in to another table, could you not use a saved query to do the job for you? Perhaps I'm so far off I think I'm close? May I see your SQL string? Gord
ghubbell@total.net

 

[TNN]

Gord,

Here is my initialize event in the class module:
Private Sub Class_Initialize()
Dim strSQLfrmPyrlCalc2 As String

'Instantiate here. Declared in General Declarations this module.
Set adoRsPyrlCalc2 = New ADODB.Recordset

'Instantiate here. Declared in General Declarations this module.
Set adoConnection = New ADODB.Connection

'Load string variable and use string in open method of adoConnection.
strConnect = "Provider=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\BEMPLOYE.MDB"
adoConnection.Open (strConnect)

'Strings to hold EXECUTE statements for stored SQL procedures to be used
'in Recordset open statements.
strSQLfrmPyrlCalc2 = "EXECUTE DisplayfrmPyrlCalc2 " & (strEmployee_Number)


'Open recordset.
'Used to load text into text boxes on frmPyrlCalc2 in form load event.
adoRsPyrlCalc2.CursorType = adOpenStatic
adoRsPyrlCalc2.LockType = adLockOptimistic
adoRsPyrlCalc2.Open Source:=strSQLfrmPyrlCalc2, _
ActiveConnection:=adoConnection, _
Options:=adCmdText
End Sub

Here is my stored procedure:

DisplayfrmPyrlCalc2
PARAMETERS [Employee_Number] IEEEDouble;
SELECT BASSEARN.EMPNO, DEPT, WRATE, MTD_AMT, QTD_AMT, YTD_AMT, WAGENAME, BASSEARN.WAGENO, FICWGE_YTD
FROM (BASSEARN INNER JOIN BASSWAGE ON BASSEARN.WAGENO = BASSWAGE.WAGENO) INNER JOIN BASSTAX ON BASSEARN.EMPNO = BASSTAX.EMPNO
WHERE BASSEARN.EMPNO =[Employee_Number];

The recordset created in the stored procedure I am using to populate text boxes using methods of the recordset.

Hope this helps you understand what I am doing. Thank You.

TNN, TOM
TNPAYROLL@AOL.COM





TOM

http://WWW.TNPAYROLL@AOL.COM

 

[ghubbell]

Tom, Nice work best as I see it!

Are you repetively doing this against the same database, If my understanding is correct, Is there any reason why you couldn't link to the database, build a query in "this" database based on "that" data? I notice this pertains to $$ so understandibly there may be security issues but if performance is what you're after, a saved query "here" should still out perform your code. Link the table and the data will be available to you as though it resided "here". Build your form based off of this data. Add a control to select or "filter" by employee number?

Have I derailed yet or is it worth a try? I'm really curious now! Gord
ghubbell@total.net

 

[TNN]

Gord,

Thank you for your input.

Yes, I am repetatively doing this against the same database. Have no concern for security issues.

Linking the database is something new to me. I will have to research. Can you give me any direction on this? Where to look??

Thank You Gord.
TNN, TOM
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[ghubbell]

Hi Tom,
Sure, as easy as hitting F1 in Access help and type in "link". This will bring up 350+ topics to choose from but the top of the list should be "link data". From there you'll find some great examples and suggestions depending on what you feel is right for your situation. Glad to help!
Gord
ghubbell@total.net

 

[TNN]

Thanks Gord
TNN, TOM
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM