Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Binding a textbox from a access query

Posted on 2011-09-15
Medium Priority
Last Modified: 2012-06-27
As much as I hate access, I am stuck creating a web page into the database. I popluate the DB just fine, and now want to show the results page but not using the standard controls, as I need to move the data around to a printer friendly form that they are used to. My thoughts were creating another form in the results page with textbox's and binding the textbox's with the recordset that they just entered. I am half way there, with using a protected sub on load, that pulls the recordset, however, does access utilize a datareader like SQL? this is the code I started but having no luck on the finish side:

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=d:\LeadSheets\App_Data\DB.mdb")
        Dim qry As String
        Dim cmd As OleDbCommand
        qry = "SELECT * FROM [Jobs] WHERE ([Job No] = @NewJob)"
        cmd = New OleDbCommand(qry, conn)
        TextBox1.Text = ????????
        Textbox2.text = ???????
Question by:BKennedy2008
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
LVL 85
ID: 36544945
So you're working in ASP.NET? And you're connecting to an Access database?

Access databases can be used with a standard OleDataAdapter if needed.

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM SomeTable", conn)
Dim ds As New Dataset

da.Fill(ds, "MyTable")

If ds.Tables("MyTable").Rows.Count > 0 Then
  For each dtr As Datarow in ds.Tables("MyTable").Rows
    Me.textbox1.text = dtr.Items("SomeField").ToString.Trim
    etc etc
  Next dtr
End If
LVL 26

Expert Comment

ID: 36544999
<grin>  When posting in the MS Access zone, it might not be such a great idea to start your question with <As much as I hate access> </grin>
Now, just for fun, for another question I just finished working out how to automate Access from C#
I realize you are using VB, but I suspect you'll be better at working the code back to VB than I would--since I do VBA and not .NET
The most important bits of it are here
You could create an Access report <grin><As much as I hate access> </grin> and have your web page print it.
And an Access report can print MUCH nice than can an HTML document--but you'd have to create a report.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Access=Microsoft.Office.Interop.Access;
using System.Reflection;

namespace testAccess
    public partial class _Default : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)


        protected void Button1_Click(object sender, EventArgs e)
        // Start a new instance of Access for Automation:
        Access.Application myAccessInstance = new Access.Application();

        // Open a database in exclusive mode:
        // Replace with your file name, not m:\\yaddayadda
           "M:\\test\\TI_Prog.mdb", //filepath
           true //Exclusive
        myAccessInstance.Visible = true;
        myAccessInstance.UserControl = true;
        //myAccessInstance.DoCmd.OpenForm ("frmCertStatus", Access.AcFormView.acNormal);
        //replace "MyReport" with what you need
        myAccessInstance.DoCmd.OpenReport ("myreport", Access.AcView.acViewNormal);


Open in new window


Author Comment

ID: 36545109

Sounds like a winner. I am installing the support for access with .net program ability support and will test out <Grin> </Grin>
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 36549509
Hmmm. The issue I have is getting the Com to register so that it recognizes the import, Imports Access = Microsoft.Office.Interop.Access

I installed the .net for Office and access using the CD, added a reference for office 12 project library to the COM, and still is doesn't recognize it, did you find another trick to making this work?
LVL 26

Expert Comment

ID: 36551329
Please understand that I am not an ASP.NET guy at all.
But I had another question that made me reseacrh the issue of Office automation from .Net and I shared the results of that.

Now, I had to set the references to  Microsoft Access 11.0 Object Library
Full blown Office is installed on my machine.
I had to add

using Access=Microsoft.Office.Interop.Access;
using System.Reflection;

manually to the top of the code module

I have pretty much the entirety of VS 2010 Express installed at the moment.
This includes the VS 2101 Tools for Office Runtime (x64)

I suspect Imports is a keyword.
You'll note that my code sample does not have the word Imports in it.
Is that relevant?

I can email you the project directory that the code came from, so you can peruse the source file
Will that be helpful?
I cannot post it as the upload won't permit most of those file types.

Author Comment

ID: 36560881
I got everything working, it pulls the report utilizing the code below, the trick is the imports, However, does anyone know how to send a variable to the access report as it needs a job number to run the report.

Imports Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access.Dao
Imports Microsoft.Office.Core

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MSA As New Microsoft.Office.Interop.Access.Application()
        MSA.Application.Visible = True
        MSA.OpenCurrentDatabase("Path to access.mdb", False)
        MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal)

    End Sub

Author Comment

ID: 36561065
I know I am close but the session variable is not populating @JobHolder, I am missing something

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MSA As New Microsoft.Office.Interop.Access.Application()
        Dim JobHolder As String
        Session("NewJob") = "2007050104"  '(For Testing purposes)
        JobHolder = Session("NewJob")
        MSA.Application.Visible = True

        MSA.OpenCurrentDatabase("D:\LeadSheets\App_Data\URIDB.mdb", False)

        MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal, WhereCondition:="[Jobs].[Job No]= @JobHolder ")

    End Sub
LVL 26

Expert Comment

ID: 36561070
Can you do me an IMMENSE favor?
Can you have a look at
And chime in to help this dude out.
It's been a PITA, but was for his question that I created the code that I posted and you found useful.

Probably the easiest way, given that this is Office automation, is to send your variable into the report via OpenArgs
OpenArgs is a string value that you can send into the report.
In the reports OnOpen() event, you can then coerce the string to whatever you need.
In the report you can use the OpenArgs value to set a global variable, build a Filter string, or base your report off a QueryDef object as your need dictates.

In the intellisense for MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal)
Its's the very last argument that it can take

Let me know if you need a hand to work that out
LVL 26

Expert Comment

ID: 36561077
Wrong quote probably
"[Jobs].[Job No]= @JobHolder "
"[Jobs].[Job No]=" & @JobHolder

Author Comment

ID: 36561135
No, I tried that, doesn't like it, its expecting expression. When I set the application to true, its asking for the variable @JobHolder
LVL 26

Accepted Solution

Nick67 earned 1000 total points
ID: 36561161
By the time it gets here

MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal, WhereCondition:="[Jobs].[Job No]= " & @JobHolder)

@JobHolder has to exist and come from someplace.
It's not coming from anything in Access, is it?
So from where in the Vb.Net stuff is it coming from?

Author Comment

ID: 36561445
Works like a champ, thanks

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim MSA As New Microsoft.Office.Interop.Access.Application()
        Dim cboJobNums As String

        cboJobNums = Session("NewJob")
        MSA.Application.Visible = False

        MSA.OpenCurrentDatabase("Path to MDB", False)

        MSA.Application.DoCmd.OpenReport("rptJobFormPrint_EDBS", AcView.acViewNormal, WhereCondition:="[Jobs].[Job No]= '" & cboJobNums & "'")



    End Sub
LVL 26

Expert Comment

ID: 36561616
Glad that for a guy who<grin><As much as I hate access> </grin>
you can kick it around pretty good and got it to work for you!

If you could have a look at that other question I posted a link to, I'd appreciate it.
The OP there doesn't seem to appreciate too much, though, so don't feel compelled to pour a lot of effort into it.


Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question