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.

 

[ccordner]

I actually have a query that just returns active staff, so I will actually get it to loop through.

Thanks for all the help with this. Looks like I should be able to do a lot with it (And hopefully get my Access skills up to the same standard as my Excel skills)!

Chris

 

[ccordner]

Thanks for that, ChrisM. I didn't know about those functions, but I am probably more comfortable with vba. I am also trying to avoid actually copying the tables into Excel if possible, as this is all (hopefully) going on 'behind the scenes'.

Chris

 

[baitmaster]

And hopefully get my Access skills up to the same standard as my Excel skills)!
Chris

I doubt it! I still know bugger all about Access, I can just import the data and play around with it in Excel... Thats why I went down this route in the first place... But maybe you'll get on better than me!

 

[ccordner]

Well, the intention is that by using Access (To be honest it still pains me to leave my Excel "comfort zone" behind) I will be forced to learn things.

I have discovered the Access forum on here is just as helpful and friendly. It's going to take a lot more getting used to though!

Chris.

 

[Ruddles]

It helps if - rather than merely regarding Access as 'where my data is' - you actually start up Access and create a couple of test tables, write some simple queries, examine the SQL they produce, etc.

It isn't really that difficult and you'll hopefully enjoy the learning experience.

 

[ccordner]

What I'm doing is all part of a wider project. One stage is to get the data out of the roster package and into Access: along with all the associated code.

Another stage is to put in forms etc (including hopefully some kind of rudimentary username system and navigation), so that people who don't use the roster package can use the database itself

One possible issue is the way our company controls user permissions.

For example, when a user of the roster package executes the code which refers to the database, presumably they will need to have the appropriate permissions to view it.

But ideally, I want to give access (through excel) to parts of the database, for people that shouldn't have access to all of it.

Granted, if they were given permission to access a certain file it's unlikely that they'd stumble across it anyway, but I wondered if there is any way around this.

To put this in context, our roster clerk and admin staff need access to it all, but some of our duty supervisors only need access to telephone numbers etc. (in case of sickness).

Presumably this depends on the way our IT department sets up user permissions but I don't even know what questions to ask about this one!

 

[ccordner]

Is it possible to use a variable to decide which procedure to run?

Say for example, I have this chunk of code for access, I want to call it from various procedures, which I can do.

But is it possible to run a subroutine, say 'ProcName', where 'ProcName' is a variable, assigned with the name of a subroutine?

So when it's going through the records of a table, I can tell it what I want to do with each one?

Thanks
Chris.

 

[Ruddles]

No, a procedure name isn't a string which you can assign to a variable - it's a symbol which is created at compile time before the code even starts to execute.

You would have to test the variable you're using as the 'switch' and call whichever procedure you wanted to run:-

select case procname
    case "add"
        call add_data
    case "reformat"
        call reformat_table
end select

 

[ccordner]

Thanks, this is coming along nicely. So far, I have the following code:

   ' 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

What I want to do is, for each record in that table, pass the values in certain fields (e.g. "Pay Number", "Name" and "Location") to a subroutine. Ideally, I want the subroutine to execute in full, for each record in the table?

Thanks
Chris

 

[Ruddles]

You do understand that you don't need to copy the recordset to a worksheet, yes? You can but you don't need to.

To pass each record to a subroutine you would have to step through the recordset something like this:-

[FONT=Fixedsys]  .movefirst[/FONT]
[FONT=Fixedsys]  do until .eof[/FONT]
[FONT=Fixedsys]    call someroutine(!PayNumber, !Name, !Location)[/FONT]
[FONT=Fixedsys]    .movenext[/FONT]
[FONT=Fixedsys]  loop[/FONT]
[FONT=Fixedsys]  .close[/FONT]

Then your subroutine would have to be set up to receive the parameters:-

[FONT=Fixedsys]sub someroutine(byval argPayNumber as long, byval argName as string, byval argLocation as string)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys][COLOR=green]' do something with argPayNumber, argName and argLocation[/COLOR][/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]end sub[/FONT]

Alternatively you can declare rst at the top of the module - before the first Sub - and then you don't need to pass anything to someroutine as rst will be available inside that subroutine.