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.
[CODE=vb]
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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenStatic
.LockType = adLockOptimisti c
.Open
.MoveLast
.MoveFirst
End With

Do While Not rstTest.EOF
MsgBox rstTest![Address]
rstTest.MoveNex t
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[/CODE]

[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:

[code=vb]
Sub GetAddress()
Dim cn As ADODB.Connectio n
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connectio n
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("Da ta Source").Value = "Name of Oracle Server"
.Properties("Us er ID").Value = "Username"
.Properties("Pa ssword").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 "CustomerLi st" 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 .ActiveConnecti on = 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 PullOneRecordIn fo()
Dim cn As ADODB.Connectio n
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connectio n
Set rs = New ADODB.Recordset

With cn
.Provider = "MSDAORA"
.Properties("Da ta Source").Value = "Name of Oracle Server"
.Properties("Us er ID").Value = "UserName"
.Properties("Pa ssword").Value = "Password"
.Open
End With
With rs
Set .ActiveConnecti on = 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("Fiel dNameInTable") '<-- Replace FieldNameInTabl e with the actual
' field that stores that value
Me.City = rs.Fields("Fiel dNameInTable") '<-- 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
[/code]

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