Integrating Excel with Access

[ccordner]

I have a roster package in Excel, which will always need to be in Excel. However, some of the functions I have built depend on lists of staff, locations etc.

The obvious thing seems to be to use Access tables, from a main database, read from Excel.

1) Is there an easy way of looping through all the records in an Access Table?

2) If the Access database is designed in two halves (front-end and back-end), would multiple Excel users be able to access the back-end?

Thanks
Chris.

 

[baitmaster]

1) Yes and No, using SQL written in VBA. Yes its quick and the machine does it easily. No its not so easy to write the code

2) Yes, multiple Excel files could look at the data at the same time

I've been doing this very thing, and eventually got somewhere. See http://www.mrexcel.com/forum/showthread.php?t=544251 for some useful stuff

 

[Ruddles]

Is there an easy way of looping through all the records in an Access Table?

I don't know about 'easy' but there are various ways of getting at data in an Access table.

You could certainly write a piece of VBA which imports a complete Access table or just a selection of records from it into an Excel worksheet which you can then reference, or a user-defined function which builds an SQL query using, for example, an employee number and then extracts that employee's details from an Access table.

You shouldn't need to loop through the table as you can run SQL queries on it from Excel and this is more efficient than opening the entire table, writing VBA code to search through it and dragging the whole lot back across the network - just select the record you want in the first place.

So yes, it's possible - you just need to know how to set up your workbook to link to an Access database and then, depending on how complex your requirements are, you need to write some SQL to extract the data you need.

If you start on the basis that anything's possible, you won't be far wrong.

So... what would you like to know first?

(Now actively seeking a Blackbird...)

 

[ccordner]

Thanks, both.

Most of the things I will want to do will be based on a query which lists all the active staff.

For example, I will need to produce a timesheet based on the roster, so for each member of staff I will need to find them on the roster and then copy their hours.

That's why I would probably loop through all the records in the query at least, unless there is a more sensible way of doing it.

At the moment, I have just created a test database and a test spreadsheet to get the principles clear in my head - I can soon adapt that later on.

I have adapted the code, shown on the linked post, but I'm not really sure what to do with the line "strQuery = "SELECT [Field1], [Field2] as BLah;".

If I wanted to return all the records in a query, how would this work?

For ease, I've copied the code below:

Sub GetAccessData()
   ' Sample demonstrating how to return a recordset from an Access db
   ' requires a reference to the Microsoft ActiveX Data Objects Library.
   
   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String, strFormula As String, i As Long
   Dim wks As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData
   
   
   ' output to activesheet
   Set wks = ActiveSheet
   
   ' Path to database
   strPathToDB = "U:\Roster Project\Test Database.mdb"
   
   Set cnn = New ADODB.Connection
   ' open connection to database
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery = "SELECT [Field1], [Field2] as BLah;"
   ' create new recordset
   Set rst = New ADODB.Recordset
   ' open recordset using query string and connection
   With rst
      .CursorLocation = adUseServer
      .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
      rst.Filter = "id = 1"
      ' check for records returned
      If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data from A2
         wks.Cells(2, 1).CopyFromRecordset rst
      End If
      .Close
   End With
   ' clean up
   Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

Thanks
Chris

 

[hgus393]

Try this from access:

You have to have a table that contains all the fields you want to import.

[VBA]
Sub GetExcel()
'
Dim sPath As String, sfile As String, sql As String, Dim MyXl As Object
Dim rs As Recordset


sPath = "C:\\Temp" ' the path to your excel spreadsheet



If Dir(sPath) = "" Then
MsgBox (sPath & " catalogue missing!")
Exit Sub
End If

sql = "DELETE * FROM [Your table]" ' Delete old data
CurrentDb.Execute sql, dbFailOnError


sfile = Dir$(sPath & "Spreadsheetname.xls") ' your spreadsheets name 'with appropirate extension
Set MyXl = CreateObject("Excel.Application")

Do While sfile <> ""
ExcelExtraction sPath, sfile, MyXl
sfile = Dir
Loop

Set MyXl = Nothing

'MsgBox ("Inläsning klar!")

End Sub

Sub ExcelExtraction(sPath As String, sfile As String, MyXl As Object)
Dim rs As Recordset
Dim i As Double
Dim varVektor As Variant
Dim sSheet As String

Set MyXl = GetObject(sPath & sfile)


sSheet = "Tab name" ' The sheets tab name
'

Set rs = CurrentDb.OpenRecordset("Your table")
i = 2 ' start line in excel
varVektor = MyXl.Worksheets(sSheet).Range("A" & i & ":K" & i).value
'range in excel you want to extract
Do While varVektor(1, 1) <> "" ' Loop until column A is blank
rs.AddNew
rs![Your first field ] = varVektor(1, 1) 'ie column A
rs![Your Second field ] = varVektor(1, 2)' ie column B
rs![Your third field ] = varVektor(1, 3)' ie column C
'etc.....
rs.Update
i = i + 1
varVektor = MyXl.Worksheets(sSheet).Range("A" & i & ":K" & i).value
Loop

MyXl.Close SaveChanges:=False
End Sub
[/VBA ]

//Robert

 

[baitmaster]

well, that bit depends on what you want to retrieve...

some of mine ended up like this

Dim strSQLstaffIDs As String: strSQLstaffIDs = "SELECT tblTimesheets.[staffID] FROM tblTimesheets WHERE tblTimesheets.[staffID] is not null " & strUnpaidOnly & strIncludeDeleted & " GROUP BY tblTimesheets.[staffID] " & "ORDER BY tblTimesheets.[staffID];"

This is SQL generated within VBA. tblTimesheets is the name of my Access table. staffID is a field within that table. "xxx.[yyy]" is the construct for referencing it via VBA. strTextStrings create other bits of text that I needed, in a similar format to what you see here

I'll look out something that pulls just a unique list of staffIDs

 

[baitmaster]

This code pulls a list of unique data, and loads it into comboboxes
strField is the name of whichever field I wanted pulled to a specific box

Sub loadComboBox(objCombo As ComboBox, strfield As String)
Application.StatusBar = "retrieving data: " & strfield
resetGlobals
 
' set database objects
Dim rsDataList As DAO.Recordset
Dim strSQL As String
 
' select unique list from database and assign to recordset object variable
strSQL = "SELECT tblTimesheets.[" & strfield & "] FROM tblTimesheets WHERE tblTimesheets.[" & strfield & "] is not null GROUP BY tblTimesheets.[" & strfield & "] " & _
    "ORDER BY tblTimesheets.[" & strfield & "];"
Set dB = OpenDatabase(strDatabase)
Set rsDataList = dB.OpenRecordset(strSQL, dbOpenDynaset)
' clear existing data from combobox
objCombo.Clear
 
' loop through recordset and populate combobox
objCombo.AddItem ""
Do While Not rsDataList.EOF
    objCombo.AddItem rsDataList(strfield)
    rsDataList.MoveNext
Loop
 
' close links
rsDataList.Close
Set rsDataList = Nothing
 
Application.StatusBar = False
End Sub

 

[Ruddles]

If I wanted to return all the records in a query, how would this work?

If you just want to open the table and cycle through the records using VBA, set your SQL string equal to the table name:-

strSQL="tblTimeSheets"

This is like saying:-

strSQL="SELECT * FROM tblTimeSheets;"

However if you can narrow down the recordset, you will speed your code up, so if your table contains records for the last twenty years for everyone who's ever worked for the company and you know you only want a subset of that data, it's better to extract only those records you're interested in.

 

[ccordner]

That's great!

I've got it to copy across all the records from tbl1 using the line:

strQuery = "SELECT tbl1.[Key] FROM tbl1 WHERE tbl1.[Key] is not null "

But in order for the code to work, I've had to comment out the line:

rst.Filter = "id = 1"

What does this actually do?

P.S. Like the combobox code, one of the many things I will need to use this for is populating comboboxes.

 

[ChrisM]

Before going down the whole ADO route, are you sure that is required? Excel has a number of built-in Access tools that don't require VBA. On the Data menu for example, you can link to either an Access Query or Table and output the results into a worksheet. These results can be refreshed at any time by right clicking and choosing refresh. You can even create queries with criteria/parameters and use the values in Excel cells to fill those criteria automatically.

Or, you can output the query results directly into a pivot table, and use =GETPIVOTDATA formulas to mine the data.