 |
| 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
|
|
|
|
October 24th, 2003, 01:39 PM
|
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
October 31st, 2003, 12:54 PM
|
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
October 31st, 2003, 03:52 PM
|
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
October 31st, 2003, 04:04 PM
|
|
Friend of Wrox
|
|
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
November 3rd, 2003, 05:36 PM
|
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hey Sal,
Thanks alot! I'm going to use the DSN file method.
Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
|
|
November 4th, 2003, 01:22 PM
|
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
|
