This is probably basic 101 for you guys as I have searched the forums and have found no questions RE: Above. Basically I started using VBA with Excel and I would like to convert a solution to VB 2005. I'm Stuck big time with retreiving information from my existing Access Database. With Excel I would use the following to populate textboxes on forms;
Sub GetProdDetails()
Dim strSQLProdDetail As String, ProdCode As String
ProdCode = frmProduct.cboProduct
strSQLProdDetail = "SELECT Product, Desc, ShipQty, ShipWT FROM tblProducts " & _
" WHERE Product= '" & ProdCode & "'"
Call connectionstringdetails (Reusable Code)
rstCurrent.Open strSQLProdDetail, cnCurrent, adOpenKeyset, adLockOptimistic
This is where I am having problems - Have set up DSN etc and can connect with no problems to the datasource.
Do While Not rstCurrent.EOF
frmProduct.tboDescription.Text = rstCurrent!desc 'tbo=TextBox
frmProduct.tboShipperWT.Value = rstCurrent!ShipWT
frmProduct.tboShipperQty.Value = rstCurrent!shipqty
rstCurrent.MoveNext
Loop
Call CloseConnection (Reusable Code)
End Sub
Can anyone offer guidence (The above code is not as pretty as some I have seen but it works - in Excel anyway!)
Mark
Excel VBA ADODB to VB 2005 Using System DSN Access Database
Tim Dawson
The reason I have gone for System DSN is this application could be deployed through a Windows Server 2000/2003 Network and as far as I know (only 1 year in Networks) it is far easier to update a users computer with a single reference to a database deployed on a network share where many users can access the same database
Mark
SuperMadAlex
Project Overview
User starts application fills in the following fields in Windows Form;
Customer (Textbox)
Order Number (Textbox - Need to find code to limit to number input)
Date Required (DateTimePicker control)
No Of Labels (Textbox - Need to find code to limit to number input)
Their Name (Returned Automatically – Windows Logon name)
Special Requirements (Textbox - Access Memo Field)
They click on an OK button and the "Order is placed" required fields written to an Access Database that resides on Network Share and a mail notification sent to Label department informing them there is a new order.
On the main form the user can select from menu strip to view outstanding orders that they have generated and also see completed orders. This information is returned back using a data grid with ODBC (Code found in forum – Credits to original poster) to a reusable form.
What I am trying to do now is return some statistics from the database. For instance;
“SELECT SUM(NoOfLabels) AS TotalUserOutstanding FROM tblOrders HAVING (COMPLETE=0) AND (User=’” & TheirName & “’)” to return the SUM of outstanding labels to a Text Box or Label and then other Stats like how many orders "COUNT".
If anyone can offer any guidence as how to return the statistics back from an Access Database using System DSN to a Textbox or Label (I can return the data to Gridview no problem just looks naff) if there a way of turning a DataGrid into (looks like) a Textbox
Any takers
Many thanks
Mark
LoganX3
The code supplied was an example of the way that I am used to working in Excel VBA. If I need to change to ADO.NET no problem - I just need help then in figuring out how to return the information back to the user using System DSN.
Thanks in Advance
Mark
NatroVert
I would like to offer the above as a solution to my problem. By changing the appearance of the DataGridView to that of a textbox I am able to return the result of query into it!
It may not be a pretty way of doing it - but it works!
Many thanks to Paul P Clement IV who tried to help - much appreciated.
Mark FMJ
Rando
Does the connection require the use of a DSN or can it be DSN-less
The reason I ask is because a DSN implements ODBC and the MS Access driver. The Jet OLEDB provider is typically the method of choice with respect to stability and support for Jet features.
Andrew Westgarth
It looks like you're still using ADO. Do you actually want to convert this to ADO.NET