Creating .exe to open access database

Hi all -

I need to create a new .exe file using VB that will open my Access DB (MHC_Database.mdb) and autofill 2 fields. It needs to fill a specific "UserID" and "Password" fields and hit enter to open the correct form (access takes it from there). I'm green to VB other than minor stuff in Access but have been told I need VB for this. Any ideas would be great. Thanks.

I'm not sure why you need a vb program to do this, but look for help on the windows api "shellexecute" you can use this api function to fire off a mdb application.

Thanks. I'll give it a shot. I'm assuming I do the search in VB itself......

There are good tutorials and articles at www.mvps.org that might give you some help.

WOW Danno, there are some touchy people on this form. I coulda told you just to look elsewhere but I'm a little nicer than that. I figure we applied on this form for a reason. (To discuss programming methods)

Here goes
This does work. I have tired it.
At the top of your code, type this.

Imports System.Diagnostics

Then, In a click event, put in this code
Note that the things within the quotes are simply the location of your database, modify that string only.

Dim MyProcess As Process = System.Diagnostics.Process.Start("C:\Documents and settings\Danno12345\My Documents\MHC_Database.mdb.exe")

remember that this is VB.NET

Naturally, you will need to set up your data adapters and data sets within the database so they can look through the records.

If you have a next button to view the next person's info, heres the code that goes behind that (you will need a binding manager set up).

Dim bmName As BindingManagerBase

Dim intRecordNumber as integer = bmName.position + 1
with bmName
If intRecord Number <.Count Then
.Position += 1
Else
btnFirst_Click(Sender, e)
end if
end with

'view Previous record
Dim intRecordNumber As integer = bmName.position
if intRecordNumber > 0 then
bmStores.Position -= 1
else
btnLast_Click(sender, e)
end if

the btnFirst & btnLast would look like this

'First record
bmName.Position = 0

'Last record
with bmName
.Position = .Count - 1
End With

If you want them to click enter to continue, simply modify the tab order so that button comes first.

Well, I hope this somewhat helped. As far as connecting a particular record up within your code to a particular form within your access database, that might be tricky but this should get you somewhere.

Filterfann

You're special, I'm sure. Pfui.

Thanks for the info Filterfann. Your code seems above my head but let me do some playing and see how far I get. Wish me luck.....many thanks.

Hi all - Ok, so I've been pluggin away on this and here's what I've come up with:

Option Compare Database
Option Explicit
Sub OpenPasswordProtectedDB()

'Define as Static so the instance of Access
'doesn't close when the procedure ends.
Static acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
strDbName = "C:\Program Files\Microsoft Office\Office10\Samples\MHCTemp.mdb"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
db.Close
Set db = Nothing
End Sub

This code opens my MS Access db just fine. What I need now is the code to pass through words in 2 text boxes on the form that automatically boots up. Let's say txtuser = "LookupID" and txtPassword = "LookupPassword" (txtuser and txtPassword are defined by the access form). I'd like VB 6.0 to open my db, automatically enter "LookupID" in the txtuser text box, tab, automatically enter "LookupPassword" in the txtPassword text box, and hit Enter. I've set up Access so it does the rest by forwarding the user to the right form. Any ideas on how to pass through those words, hit tab, and hit enter? Any help would be great. Thanks.

Since your Access App is open you should be able to manipulate any forms or controls from your VB code, but I don't know details.

Or you could use ADO from your VB code in a similar manner to the way you use ADO from asp pages. Open a connection to the mdb, and build a suitable SQL string to update the proper columns. If you do this first, and your textboxes are data bound, the changes should be there when you open the access application.

Thanks for the reply. Not sure what ADO is....I'll have to look into it. I was hoping I could just add a couple lines of code using a pass through type of function, that would auto fill my 2 text boxes. Maybe I'm way off base......do you know if this could be done?

Do you want the text to be visible in the two text boxes (when the program runs) or do they need to enter the information first? If you need the text boxes auto filled with names from a database, I can try and get you some code (I only know VB.NET in case you are using 6.0) for that part.

Hi filterfann - I'm using 6.0 although I can try to adapt it. Thanks for offering to send along code....anything will be a blessing at this point! I've been on the internet and in VBV books everywhere but can't seem to get this last part. Yes, the text can be visible. The plan is to make an executable of this VB pgm and give it to my 20+ users to put on their desktops. When they double-click on it, it should open my Access database (on a server) and automatically fill the above text boxes. I've set up Access so that the correct userID and password will branch the user to a form where they can lookup patients in the db. Hope this makes sense.....

Thanks loads.

Danno:
Here is some VB.NET code from a project I have. Just take what you need from it. VB.NET also has a Server explorer allowing programmers to drag fields from tables into programs as data adapters, connections, and data sets but i dont know if 6 has that so here is how to hard-code data into controls in a form. Let me know if you have any questions about the code and Ill get back to you. The comments should be somewhat explanitory but if not, let me know and I'll help out where I can. This ode also allows you to, add, edit, update, and delet records from the database. Hope this was helpfull.

'option strict Must be on
Option Strict On

Public Class frmRnR
Inherits System.Windows.Forms.Form

Dim dataBooks As RnRData 'Instance of data tier component
Dim dsRnRData As DataSet
Dim bmBooks As BindingManagerBase

Private Sub frmRnR_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Retrieve the dataset from the data tier component

Try
dataBooks = New RnRData() 'Instantiate data component
dsRnRData = dataBooks.getData 'Retrieve dataset

'Get the BindingManagerBase for the books table
bmBooks = Me.BindingContext(dsRnRData, "Books")

'Connect to the combo box
'(This must be done before binding the combo box to display the
' first record correctly.)
With cboSubjectCode
.DataSource = dsRnRData.Tables("subjects")
.DisplayMember = "Subject"
.ValueMember = "SubjectCode"
.Enabled = False
End With

'Bind the data fields
BindData()

'Add the delegate for the PositionChanged event
AddHandler bmBooks.PositionChanged, AddressOf Position_Changed

'Display the record number for the first record
Position_Changed(sender, e)

Catch err As Exception
MessageBox.Show(err.Message, "Error")
End Try
End Sub

Private Sub btnFirst_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnFirst.Click
'Move to the first record

bmBooks.Position = 0
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnNext.Click
'Move to the next record
Dim intRecordNumber As Integer = bmBooks.Position

If intRecordNumber <> bmBooks.Count - 1 Then
bmBooks.Position += 1
Else
btnFirst_Click(sender, e)
End If
End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnPrevious.Click
'Move to the previous record
Dim intRecordNumber As Integer = bmBooks.Position

If intRecordNumber <> 0 Then
bmBooks.Position -= 1
Else
btnLast_Click(sender, e)
End If
End Sub

Private Sub btnLast_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLast.Click
'Move to the last record

With bmBooks
.Position = .Count - 1
End With
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAdd.Click
'Begin an Add operation or cancel the current operation

If btnAdd.Text = "&Add" Then
UnlockTextBoxes()
DisableNavigation()
SetButtonsForEdit()
'Make sure current record is saved
bmBooks.EndCurrentEdit()
'Unbind the check box (required to work around bug)
With chkFiction
.DataBindings.Clear()
.Checked = False
End With
'Clear the fields
bmBooks.AddNew()
cboSubjectCode.SelectedIndex = -1
cboLocation.SelectedIndex = -1
With txtISBN
.ReadOnly = False
.Focus()
End With
Else 'Cancel button clicked
bmBooks.CancelCurrentEdit()
LockTextBoxes()
EnableNavigation()
ResetButtonsAfterEdit()
End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnDelete.Click
'Delete the current record after confirming
Dim dgrDelete As DialogResult

Try
dgrDelete = MessageBox.Show("Delete this record?", _
"Confirm Delete", MessageBoxButtons.YesNo)
If dgrDelete = DialogResult.Yes Then
With bmBooks
.RemoveAt(.Position)
End With
dataBooks.update(dsRnRData)
dsRnRData.AcceptChanges()
End If
Catch err As Exception
MessageBox.Show(err.Message)
End Try
End Sub

Private Sub btnEdit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnEdit.Click
'Begin an Edit operation for the current record

DisableNavigation()
UnlockTextBoxes()
SetButtonsForEdit()
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSave.Click
'Save updates to the dataset

Try
bmBooks.EndCurrentEdit()
If (dsRnRData.HasChanges()) Then
dataBooks.update(dsRnRData)
dsRnRData.AcceptChanges()
LockTextBoxes()
EnableNavigation()
ResetButtonsAfterEdit()
HandleCheckBoxBinding()
End If

Catch err As Exception
'Check for duplicate records and constraint violations
MessageBox.Show(err.Message)
End Try
End Sub

Private Sub BindData()
'Bind the dataset to the controls

txtISBN.DataBindings.Add("text", dsRnRData, "Books.ISBN")
txtTitle.DataBindings.Add("text", dsRnRData, "Books.Title")
txtPublisher.DataBindings.Add("text", dsRnRData, "Books.Publisher")
txtAuthor.DataBindings.Add("text", dsRnRData, "Books.Author")
cboSubjectCode.DataBindings.Add("SelectedValue", dsRnRData, "Books.Subject_Code")
cboLocation.DataBindings.Add("text", dsRnRData, "Books.Shelf_Location")
chkFiction.DataBindings.Add("checked", dsRnRData, "Books.Fiction")
End Sub

Private Sub DisableNavigation()
'Disable the navigation buttons

btnFirst.Enabled = False
btnLast.Enabled = False
btnPrevious.Enabled = False
btnNext.Enabled = False
End Sub

Private Sub EnableNavigation()
'Enable the navigation buttons

btnFirst.Enabled = True
btnLast.Enabled = True
btnPrevious.Enabled = True
btnNext.Enabled = True
End Sub

Private Sub HandleCheckBoxBinding()
'Handle the check box binding on an Add
'Note: This routine is needed to work around a bug that occurs
' when binding a check box to a database field and
' an AddNew occurs. You must unbind the check box before the
' AddNew and bind it again afterward.

If IsNothing(chkFiction.DataBindings.Item("Checked")) Then
'Set the value of the field to match the check box
Dim drCurrentRow As DataRowView
drCurrentRow = CType(bmBooks.Current, DataRowView)
drCurrentRow.Item("Fiction") = chkFiction.Checked
'Bind the check box
chkFiction.DataBindings.Add("Checked", dsRnRData, "books.Fiction")
End If
End Sub

Private Sub Position_Changed(ByVal sender As Object, _
ByVal e As EventArgs)
'Display the record position

With bmBooks
sbpPositionPanel.Text = "Record " & (.Position + 1).ToString & _
" of " & .Count.ToString
End With
End Sub

Private Sub ResetButtonsAfterEdit()
'Reset the buttons after an Add or Edit operation

btnAdd.Text = "&Add"
btnSave.Enabled = False
btnDelete.Enabled = True
btnEdit.Enabled = True
End Sub

Private Sub SetButtonsForEdit()
'Set up the buttons for an Add or Edit operation

btnAdd.Text = "&Cancel"
btnSave.Enabled = True
btnDelete.Enabled = False
btnEdit.Enabled = False
End Sub

Private Sub LockTextBoxes()
'Lock after Add or Edit is complete

txtISBN.ReadOnly = True
txtTitle.ReadOnly = True
txtAuthor.ReadOnly = True
txtPublisher.ReadOnly = True
cboSubjectCode.Enabled = False
cboLocation.Enabled = False
chkFiction.Enabled = False
End Sub

Private Sub UnlockTextBoxes()
'Unlock for Add or Edit

txtTitle.ReadOnly = False
txtAuthor.ReadOnly = False
txtPublisher.ReadOnly = False
cboSubjectCode.Enabled = True
cboLocation.Enabled = True
chkFiction.Enabled = True
End Sub
End Class

Like I said, let me know if you need more explanation on something and I'll try and help. I was in a hurry when I pasted this code in.

Don't get confused Danno.
FilterFann is posting VB.NET code, not Visual Basic code.
There's a big difference.

As for you being helpful Filterfan...you would be if you posted the code in the proper language.

One would assume since you posted this question in the Visual Basic forum, rather than the .NET forum that you are using VB 6.0 or earlier, not .NET.