Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 24th, 2003, 01:39 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Bob,
Thanks for you suggestions! I used you reply as a guide but I did it a bit differently from the code you provided me with. I will post the code for you to check out and for other to see the solution to one of my many problems :

Code:
Dim db As ADODB.Connection
Dim qrlicno As ADODB.Command
Set qrlicno = New ADODB.Command
Dim rslicno As ADODB.Recordset
Set rslicno = New ADODB.Recordset
Dim prlicno As ADODB.Parameter
Set prlicno = New ADODB.Parameter

Dim strSQL As String
Dim strCriteria As String

strCriteria = Me.Lic_No.Text
strSQL = "SELECT [_Lab Identification Table].* FROM [_Lab Identification Table] " & _
"WHERE (([_Lab Identification Table].Lic_No)= ? );"

Set db = CurrentProject.Connection
qrlicno.ActiveConnection = db
qrlicno.CommandText = strSQL
qrlicno.CommandType = adCmdText
Set prlicno = qrlicno.CreateParameter("Lic_No", adChar, adParamInput, 5, strCriteria)
qrlicno.Parameters.Append prlicno


If IsNull(Me.Lic_No) Then
    MsgBox "Licence Number was left empty." & vbNewLine & "Select View or Add button then enter a licence number", vbOKOnly + vbCritical, "Licence Number"
    Exit Sub
Else
    If Len(Me.Lic_No) <> 4 Then
        MsgBox "Length of Licence Number must be 4 digits", vbOKOnly + vbCritical, "Licence Number"
    Else
        Set rslicno = qrlicno.Execute
        If Me.Lic_Choice = 1 Then 'View Licence Number
            MsgBox rslicno.EOF & "++++" & rslicno.BOF
            If rslicno.EOF And rslicno.BOF Then
                MsgBox "Licence Number entered does not exist" & vbNewLine & "To ADD a new licence, select Add New Licence", vbOKOnly + vbCritical, "Licence Number"
                Me.Lic_Choice.SetFocus
            End If
        ElseIf Me.Lic_Choice = 2 Then  'Add Licence Number
            MsgBox rslicno.EOF & "++++" & rslicno.BOF
            If Not rslicno.EOF And Not rslicno.BOF Then
                MsgBox "im in"
                MsgBox "CAUTION: Licence Number entered currently exists" & vbNewLine & "To VIEW licence, select View Licence", vbOKOnly + vbCritical, "Licence Number"
                Me.Lic_Choice.SetFocus
            End If
        End If
        If Left(Me.Lic_No, 1) <> "3" Then
            Me.Class___Test.Enabled = True
        Else
            Me.Class___Test.Enabled = False
        End If
        Me.Laboratory_Organization.SetFocus
    End If
End If
rslicno.close
Set rslicno = Nothing
End Sub
Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old October 31st, 2003, 12:54 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello there,
Can any one tell me how updating a recordset in ADO works? I am trying to update a recordset with adOpenKeyset and adLockOptimistic but I cannot update the recordset, I get an run-time error: "Cannot update. Database or object is read-only."
i get the error on
Code:
rslic![Lic_No] = Me.Lic_No
thnx in advance!

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old October 31st, 2003, 03:52 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hey guys,
I kinda figured out my problem. I was using the current connection of the access database on linked table (to back-end SQL server) so somehow I couldn't update from there. What I did for now is i made a direct OLE DB conenction to SQL server and it seems likes its working hehe!
But I am still confused about how this business works! Maybe if i explain my database layout, someone can think of an efficient way!
I have an front end access 2000 DB running from backend SQL server database. my tables are linked from the SWQL server DB with an ODBC connection(DSN) based on my win login.
Is it possible to use that ODBC (DSN) connection that the access 2000 uses to link the tables to retrieve/,odify data? if so how?
If anyone knows any good tutrials, I d be happy to look at those too!

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old October 31st, 2003, 04:04 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, you can use that DSN from access do update/add/delete the data, as long as the user has the proper permissions.
You can update the data directly from the tables.
Read books online, it has info on this subject. Windows Authentication is much easier and secure to implement.
Also, use a file dsn. this makes it easier for users to use the database front end from any machine.



Sal
 
Old November 3rd, 2003, 05:36 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Sal,
Thanks alot! I'm going to use the DSN file method.

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old November 4th, 2003, 01:22 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello again,
I dunno if this topic belongs here or not, but i ll ask my question here only! In the database I'm working on for most of the forms, there is a query which fills in the textboxes. So the forms' record source connects the form to a query in the database. The data in the fields are retreived properly, my problem is that i need to update some of the fields and I can't since my backend is on a SQL server and the query is just a select statement. I guessed I should just get rid of the query and use a recordset to retrieve the data from the sql server then use rst.upate to update the fields. If there is an easier/more effiecnt way I'm all ears. If what i just said doesnt make sense please let me know so I can explain again!

thanks,

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW





Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO or DAO knowledge76 Access VBA 3 October 12th, 2015 04:26 PM
how to convert this DAO connection code to ADO Con ruth26 VB Databases Basics 0 May 11th, 2006 05:15 AM
how to translate a code in DAO to ADO itsmenow Access VBA 2 July 7th, 2005 09:19 AM
DAO / ADO? merguvan Access VBA 8 January 18th, 2004 07:39 AM
How to connect .dbf file with ADO & DAO with code cbpanchal VB How-To 4 July 28th, 2003 10:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.