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

[ndeeley]

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

[ADezii]

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.

Dim strSQL As String
Dim rstTest As New ADODB.Recordset
 
 
 
If Not IsNull(Me![City]) Then
  strSQL = "Select * From dbo_Employees Where [City] = '" & Me![City] & "';"
  With rstTest
    .Source = strSQL
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
      .Open
        .MoveLast
        .MoveFirst
  End With
 
  Do While Not rstTest.EOF
    MsgBox rstTest![Address]
    rstTest.MoveNext
  Loop
Else
  Exit Sub
End If
 
'Set the Form's Recordset to the ADO Recordset
Set Me.Recordset = rstTest
 
'Should not Close this Recordset in this Scope
'rstTest.Close
'Set rstTest = Nothing

[ndeeley]

Thats great - I'll give it a go and let you know how I get on!

Cheers
Neil

[PianoMan64]

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:

Sub GetAddress()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With cn 'This section allows you to directly connect using OLEDB to your oracle server.
'You will have to replace what I've put in Quotes with the actual names of these
'Values. If you're not sure what the values are, check with your IT Department.
.Provider = "MSDAORA"
.Properties("Data Source").Value = "Name of Oracle Server"
.Properties("User ID").Value = "Username"
.Properties("Password").Value = "Password"
.Open
End With
With rs
'This section connects you to the particular Oracle table that you're wanting
'to do the search against. Please note I used "CustomerList" as the name of the
'table. You will have to replace it with the actual name of the table.
'Also I'm assuming that you have a Control on your form that is named PostalCode
'This is what you're going to set the postalcode value from.
Set .ActiveConnection = cn
.Source = "SELECT * FROM CustomerList WHERE PostalCode = " & Me.Postalcode
.LockType = adLockReadOnly
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
'This Set Option is to set the recordset returned from Oracle and binds it to a list control
'that is on your form called ListControl. Replace that with the name of the list control on your
'form. Please note that the number of columns returned will have to be set in the List Control
'in order the information to return all the columns of data. By defualt the list control is set
'to 1, you'll need to change to the number of columns that you want to return from oracle.
 
Set Me.listcontrol.Recordset = rs
End Sub
'Now also keep in mind that I'm assuming that all the records in this table that you're pulling from
'also contains a Primary Key for Each entry in the able. if not, this example will not work.
Sub PullOneRecordInfo()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
With cn
.Provider = "MSDAORA"
.Properties("Data Source").Value = "Name of Oracle Server"
.Properties("User ID").Value = "UserName"
.Properties("Password").Value = "Password"
.Open
End With
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM CustomerList WHERE RecordID = " & Me.listcontrol.Value
.LockType = adLockReadOnly
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
 
If Not rs.EOF Then
Me.StreetNo = rs.Fields("FieldNameInTable") '<-- Replace FieldNameInTable with the actual
' field that stores that value
Me.City = rs.Fields("FieldNameInTable") '<-- Same Here
'and so on
End If
 
rs.Close '<-- A little House Keeping that needs to happen everytime you open a connection
cn.Close '<-- same here
 
Set rs = Nothing '<-- And Here
Set cn = Nothing '<-- and here also
 
Me.listcontrol.Requery
Me.Repaint
End Sub
 

Hope that helps a little. If you have any questions, please let me know.

Joe P.

[ADezii]

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).

[PianoMan64]

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.

[ndeeley]

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