Get names from Access table

[Tempy]

Good morning,
Not being a programmer, but a dabbler i am not sure if the following is
possible ?

Retrieve names from a table (tblQualityStaff)in Access file (New.mdb)to
insert into a spread sheet in Excel.

If it is possible, could you help with some code please.

Thanks in Advance

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

You don't actually need code. (If you want to use code then look up ADO DB
Recordset). Go to Data -> Import External Data -> Data Base Query... then
just follow the wizard.

 

[Tempy]

Hi Jim, thanks for the reply, but i want to do it as part of other code
that i am using to manipulate a spreadsheet so therefore do not want to
do this manually

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

Here is a function that runs a query against a database. You need to
understand the select statement to use this. You must also reference the
Microsoft ADODB Recordset in the VBE. The function returns a recordset object
that you can use. m_cDBLocation is a module level variable which defines the
name and location of the database.

Private m_cDBLocation as string = "C:\New.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

The function is used something like this

Dim rst As ADODB.Recordset
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strOrderBy As String

'Create SQL statement
strSelect = "SELECT Max(tblVehicleHeader.[Unit Number]) AS [MaxOfUnit
Number]"
strFrom = "FROM tblVehicleHeader"
strWhere = ""
strOrderBy = ";"

Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True)

The final argument specifies that I want a connected recordset which allows
me to write back to the database using .update

 

[Tempy]

Thanks Jim

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

 

[TK]

Hi: Tempy

Here is another example of ADO code, along with Jim's it should
give you a good understanding of the process. This example retrives
data from the nwind db that comes with Access. Cut and paste the
procedure and follow the comments.

Good Luck
TK

..........................................................

Private Sub CommandButton4_Click()

On Error GoTo ErrHandler

Dim rg As Range
Set rg = ThisWorkbook.Worksheets(2).Range("a1")

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'>Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'You should fully quality the path to your file

Dim db_Name As String
db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
Dim DB_CONNECT_STRING As String

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data Source=" & db_Name & ";" & ", , , adConnectAsync;"

'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING

'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "SELECT CompanyName, ContactName, City, Country " & _
"FROM Customers ORDER BY CompanyName"

'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic

'Test to see if we are connected and have records
Dim num As Integer
num = rs.RecordCount

Dim num1 As Integer
num1 = rs.Fields.Count

If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name & " Records = " & num & " Fields =
" & num1, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Copy recordset to the range
rs.MoveLast
rs.MoveFirst
rg.CopyFromRecordset rs
rg.CurrentRegion.Columns.AutoFit

'close connection
cnn.Close
Set cnn = Nothing
Set rs = Nothing

Exit Sub

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
End Sub

...............................................................