Using Macros

[DDONNI]

Hi Huys i'm really having problems with macros as i've never used them in Access. I'm used to recording a marco in Excel but i can't seem to do that here.

What i'm trying to do is create a marco that will upload 3 spreadsheets into 3 tables which i've already created in access. The tables are blank so its just a case of transferring the information. But i have no idea how to do this. Could someone detail how i should go about this please, its bugging me so much.

Cheers,

Donni

 

[sfreeman@co.mer]

Link instead??

Donni:

If you are just trying to get the Excel data into Access, why not just link to the spreadsheets as attached tables? This will give you the advantage of still having all of the Excel functionality in the spreadsheet.

If you really want to use a macro,

select TransferSpreadsheet as your first action.

Down below, select Import, set the name of the table you have created for that spreadsheet, the version of Excel, whether or not you want the field names to be used in the table, and whether or not you want to only import a range from the spreadsheet.

Check the Access help file, it really is quite clear.

HTH

 

[DDONNI]

Ok Cheers thats really great, though how do you link the Excel with Access and is it dynamic?

 

[Mile-O]

Yes, a linked spreadsheet as a table is dynamic.

Regarding macros, I'd advise against using them in Access - you are much better off to write your own VBA code (much the same as Excel macros) to do what you want.

Macros, in Access, aren't very flexible and have no error handling capabilities.

 

[DDONNI]

Cheers, i think i might stay off the macros then i don't know that much about VBA other than i can only read it.

How do you set up the link between excel tables and Access tables. I can't find a button or option anywhere?

 

[Mile-O]

On the Tables tab of the Database Window, right click and select Link. Change the file type from database to Excel workbook.

The spreadsheet should be in the same format as you'd expect a database table otherwise there wil be errors.

 

[DDONNI]

Ok its slightly more complex than that i've got 15 tables in Access and i need to link them to 15 tables in an Excel workbook. How do i do that?

 

[DDONNI]

OK i've managed to link a table up in excel, but i can't seem to delete records in Access as its telling me

"Deleting data in a linked table is not supported by this ISAM"

Can anyone help me on this please i'm totally lost.

Effectively what i'm trying to do is load up 3 tables from excel use queries to create 15 new tables and then have those tables linked to a different excel workbook where there formulas that manipulate it further and create a load of tables and graphs.

Is this possible using Linked tables, as at the minute the queries are set to create a new table each time i run them which overwrites the old table.

 

[sfreeman@co.mer]

http://www.vb123.com/toolshed/98docs/excelbe.htm

From some text:

Modifying Data

If you want to start adding data management facilities through Access, you need to add visual basic code to give Excel full database functionality. The main issue is that the Excel linker will not allow you to delete a row of data. When deleting an Excel data row through Access, the following error occurs

Deleting data in a table is not supported by this ISAM

But Access will allow you to add new rows of data (INSERT) and change existing data (UPDATE). So the technique that was established to get around the delete record error was to set every field in the current row of data to null. See the code in Listing 5 to see how this is handled in the On Delete Event in the data management form.

Private Sub Form_Delete(Cancel As Integer)

' Using Excel as a backend database will not support deletions.

' So instead set all the fields in the current record to null and

' cancel the deletion



Me![ContactID] = Null

Me![FirstName] = Null

Me![LastName] = Null

…

Me![ContactTypeID] = Null

Me![ReferredBy] = Null

Me![Notes] = Null

Cancel = True

End Sub

Maybe it wouldb e better for you to import the data into Access tables.

To get an idea of how VBA works, first create your macro, then under the Tools menu, then macro, you can convertr the macro to VBA. Be sure to include the error handling.

HTH

 

[DDONNI]

Cheers mate i'll have a look and give it ago. I think i'm finally getting the hang of Access. Though it taken ages. .

 

[sfreeman@co.mer]

Hang in there....

Non Illegitimi Carborundum !!