By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Retrieve information from a linked database & spray the results back into Access

100+
P: 139
Hi!

I'm hoping someone can help me get started on a form button I need to build in Access 2002.

I'm revamping an old database that uses alot of code and I`m not that hot on Access so here is what I need to do if someone can give me some tips on how to get started...

1) Take a postcode from a text box and query it against an Oracle linked table
2) Return all the addresses from that postcode in a form, listed by rows
3) When one of the rows is clicked information from the postcode autofills most of the boxes on my existing form (such as House No, Street, Town etc), closing down the results form in the process.

I can get by using queries I have build before in Access but I haven't got much experience on SQL so any help you could give would be great.

Thanks
Neil
Apr 17 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,601
Hi!

I'm hoping someone can help me get started on a form button I need to build in Access 2002.

I'm revamping an old database that uses alot of code and I`m not that hot on Access so here is what I need to do if someone can give me some tips on how to get started...

1) Take a postcode from a text box and query it against an Oracle linked table
2) Return all the addresses from that postcode in a form, listed by rows
3) When one of the rows is clicked information from the postcode autofills most of the boxes on my existing form (such as House No, Street, Town etc), closing down the results form in the process.

I can get by using queries I have build before in Access but I haven't got much experience on SQL so any help you could give would be great.

Thanks
Neil
I'm not too sure about Oracle, but I successfully ran the following code against a ODBC, Linked SQL Server Table (Employees Table in the Northwind Database). I created an ADO Recordset on the Employees Table based on the value entered in the [City] Field and returned Addresses for those Cities. I then set the Recordset property of the Form to this Recordset assuming the Form was in Continuous View and had a Field previously Bound to Address. Let me know if you have any questions.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim rstTest As New ADODB.Recordset
  3.  
  4.  
  5.  
  6. If Not IsNull(Me![City]) Then
  7.   strSQL = "Select * From dbo_Employees Where [City] = '" & Me![City] & "';"
  8.   With rstTest
  9.     .Source = strSQL
  10.     .ActiveConnection = CurrentProject.Connection
  11.     .CursorType = adOpenStatic
  12.     .LockType = adLockOptimistic
  13.       .Open
  14.         .MoveLast
  15.         .MoveFirst
  16.   End With
  17.  
  18.   Do While Not rstTest.EOF
  19.     MsgBox rstTest![Address]
  20.     rstTest.MoveNext
  21.   Loop
  22. Else
  23.   Exit Sub
  24. End If
  25.  
  26. 'Set the Form's Recordset to the ADO Recordset
  27. Set Me.Recordset = rstTest
  28.  
  29. 'Should not Close this Recordset in this Scope
  30. 'rstTest.Close
  31. 'Set rstTest = Nothing
Apr 19 '08 #2

100+
P: 139
Thats great - I'll give it a go and let you know how I get on!

Cheers
Neil
Apr 22 '08 #3

Expert 100+
P: 374
Thats great - I'll give it a go and let you know how I get on!

Cheers
Neil
Hey Neil,

Since I work with Oracle every day, I have another suggestion. It is simular, but it allows to work with Oracle Directly, and not have to go through an ODBC connecter, They are very slow, and this option gives you a better performance hit than going through ODBC.

Example:

Expand|Select|Wrap|Line Numbers
  1. Sub GetAddress()
  2. Dim cn As ADODB.Connection
  3. Dim rs As ADODB.Recordset
  4.  
  5. Set cn = New ADODB.Connection
  6. Set rs = New ADODB.Recordset
  7. With cn 'This section allows you to directly connect using OLEDB to your oracle server.
  8. 'You will have to replace what I've put in Quotes with the actual names of these
  9. 'Values. If you're not sure what the values are, check with your IT Department.
  10. .Provider = "MSDAORA"
  11. .Properties("Data Source").Value = "Name of Oracle Server"
  12. .Properties("User ID").Value = "Username"
  13. .Properties("Password").Value = "Password"
  14. .Open
  15. End With
  16. With rs
  17. 'This section connects you to the particular Oracle table that you're wanting
  18. 'to do the search against. Please note I used "CustomerList" as the name of the
  19. 'table. You will have to replace it with the actual name of the table.
  20. 'Also I'm assuming that you have a Control on your form that is named PostalCode
  21. 'This is what you're going to set the postalcode value from.
  22. Set .ActiveConnection = cn
  23. .Source = "SELECT * FROM CustomerList WHERE PostalCode = " & Me.Postalcode
  24. .LockType = adLockReadOnly
  25. .CursorType = adOpenKeyset
  26. .CursorLocation = adUseClient
  27. .Open
  28. End With
  29. 'This Set Option is to set the recordset returned from Oracle and binds it to a list control
  30. 'that is on your form called ListControl. Replace that with the name of the list control on your
  31. 'form. Please note that the number of columns returned will have to be set in the List Control
  32. 'in order the information to return all the columns of data. By defualt the list control is set
  33. 'to 1, you'll need to change to the number of columns that you want to return from oracle.
  34.  
  35. Set Me.listcontrol.Recordset = rs
  36. End Sub
  37. 'Now also keep in mind that I'm assuming that all the records in this table that you're pulling from
  38. 'also contains a Primary Key for Each entry in the able. if not, this example will not work.
  39. Sub PullOneRecordInfo()
  40. Dim cn As ADODB.Connection
  41. Dim rs As ADODB.Recordset
  42.  
  43. Set cn = New ADODB.Connection
  44. Set rs = New ADODB.Recordset
  45.  
  46. With cn
  47. .Provider = "MSDAORA"
  48. .Properties("Data Source").Value = "Name of Oracle Server"
  49. .Properties("User ID").Value = "UserName"
  50. .Properties("Password").Value = "Password"
  51. .Open
  52. End With
  53. With rs
  54. Set .ActiveConnection = cn
  55. .Source = "SELECT * FROM CustomerList WHERE RecordID = " & Me.listcontrol.Value
  56. .LockType = adLockReadOnly
  57. .CursorType = adOpenKeyset
  58. .CursorLocation = adUseClient
  59. .Open
  60. End With
  61.  
  62. If Not rs.EOF Then
  63. Me.StreetNo = rs.Fields("FieldNameInTable") '<-- Replace FieldNameInTable with the actual
  64. ' field that stores that value
  65. Me.City = rs.Fields("FieldNameInTable") '<-- Same Here
  66. 'and so on
  67. End If
  68.  
  69. rs.Close '<-- A little House Keeping that needs to happen everytime you open a connection
  70. cn.Close '<-- same here
  71.  
  72. Set rs = Nothing '<-- And Here
  73. Set cn = Nothing '<-- and here also
  74.  
  75. Me.listcontrol.Requery
  76. Me.Repaint
  77. End Sub
  78.  
Hope that helps a little. If you have any questions, please let me know.

Joe P.
Apr 22 '08 #4

ADezii
Expert 5K+
P: 8,601
Hello PianoMan64, it's nice to now know that we now have a resident Oracle Expert here in the Access Forum. We'll definately keep you in mind when Oracle/Access interface questions arise. I'll talk to Mary about giving you a raise in salary, but I wouldn't hold my breath on it. (LOL).
Apr 22 '08 #5

Expert 100+
P: 374
Hello PianoMan64, it's nice to now know that we now have a resident Oracle Expert here in the Access Forum. We'll definately keep you in mind when Oracle/Access interface questions arise. I'll talk to Mary about giving you a raise in salary, but I wouldn't hold my breath on it. (LOL).
Alright, thanks for the compliment.

Joe P.
Apr 24 '08 #6

100+
P: 139
Alright, thanks for the compliment.

Joe P.

Hi Joe,

Thanks very much - I`ll give it a go and let you know how I get on!

Cheers
Neil
Apr 24 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.