Allistaire,
I hate leaving people hanging - but I still recommend reading the
information provided by others in other posts - much to learn.
In the meantime, here's a fully functioning Excel macro that I built as an
example for you. Hopefully you can follow along and see what's needed and
how things would be done. Obviously the path to the .mdb file, the table
name and the fields of the table will different, but the process and logic is
sound.
Sub ConnectToDatabase()
'uses DAO datasets vs ADO
'needs Tools | References to:
' MS Office xx Object Library
' Microsoft DAO 3.6 Object Library (or 3.51)
'
'to set up the references, open the VB Editor [Alt]+[F11] and
'choose Tools then References from the VB Editor's menu.
'
'change these constants as required
'full, well-formed path to the database, can use network type path
Const PathToDatabase = "G:\Contracts\Contracts.mdb"
Const tableName = "tblContracts"
Dim wkSpace As DAO.Workspace
Dim dbObject As DAO.Database
Dim anyTable As DAO.Recordset
Dim sqlStatement As String
Dim anyRange As Range
Dim contractID As String
Dim LC As Long ' loop counter
'presume Contract ID to get info about is in on Sheet1, cell A1 and
'we want to put returned data in columns B, C, D, etc
Set anyRange = Worksheets("Sheet1").Range("A1")
If IsEmpty(anyRange) Then
MsgBox "Please Provide a Contract ID", vbOKOnly, "No Contract ID"
anyRange.Select
Set anyRange = Nothing 'release resources
Exit Sub
End If
contractID = anyRange.Value
'seem ready to go, give it a try
Set wkSpace = DAO.CreateWorkspace("myWorkspace", "admin", "")
'if the database requires userID and password to open, you'll
'need to provide those as additional parameters in next command.
Set dbObject = wkSpace.OpenDatabase(PathToDatabase)
'build an SQL statement to include the contractID for retrieval
sqlStatement = "SELECT tblContracts.* FROM tblContracts WHERE " & _
"(((tblContracts.[Contract ID])='" & contractID & "'));"
Set anyTable = dbObject.OpenRecordset(sqlStatement)
'the returned records, if any, are now in anyTable
On Error GoTo ErrorDuringRetrieval
anyTable.MoveLast
anyTable.MoveFirst ' gives accurate recordcount
On Error GoTo 0 ' clear error trapping
'move our individual recordset fields into row below the Contract ID entry
at A1
For LC = 1 To anyTable.RecordCount
'we could do this with 2 loops, but we'll keep it straight-forward
With anyTable
anyRange.Offset(LC, 0) = ![Contract ID] ' field name
anyRange.Offset(LC, 1) = ![ContractName] ' field name
anyRange.Offset(LC, 2) = ![Contract Manager] ' field name
anyRange.Offset(LC, 3) = ![ContractValue] ' field name
anyRange.Offset(LC, 4) = ![StaffSize] ' field name
End With
Next
ExitConnToDB:
On Error Resume Next ' just grease on through
anyTable.Close
Set anyTable = Nothing
dbObject.Close
Set dbObject = Nothing
wkSpace.Close
Set wkSpace = Nothing
Set anyRange = Nothing
On Error GoTo 0 ' clear any error trapping still in effect
Exit Sub ' stay out of error handling territory
ErrorDuringRetrieval:
If Err.Number = 3021 Then
'simply no records returned
MsgBox "No records matching the entered Contract ID found.", _
vbOKOnly, "No Match"
Else
'something probably unexpected
MsgBox "Error: " & Err.Number & vbCrLf & Err.desctiption, _
vbOKOnly, "Error Encountered"
End If
Resume ExitConnToDB
End Sub
Alistaire Green said:
Thanks Jlathem,
I uderstand the code you have typed, but im a little confused how we can get
other cells in a excel sheet to populate accourding to the Contract Number is
in the Cell.
Eg
Cell A1 is where you type the contract Number.
Then from putting the Contract Number in cell A1 all the other cells which
automatically populate.
Not know if im onto a loose end but if there is further help you can give fr
me to get my head wrong this i would appreciate it