Editing Macro Visual Basic Code

[NICOLE2000]

Hi,

I have created a macro using Access 2000. I started by clicking "Macro" in the database window and then clicking "Design". Then I created the macro by choosing options from the drop-down boxes. So, I didn't do any coding, just created it quite simply in design view. Now, I want to make some changes to the macro's visual basic code. How do I view the source code behind the macro I created to make changes to it?

[Mongo]

In Access, right click the macro.
Choose "Save As/Export."
Choose "Save as Visual Basic Module."
Then open the newly create (converted macro) in the Module.

[NICOLE2000]

Mongo,

Thanks for your quick reply, I was able to view the code immediately. I have another question. The reason I need to change the code is because I am using the DoCmd.TransferSpreadsheet method to import an Excel spreadsheet. Access is looking at a field that contains numerical data for the first 200 lines and assuming the entire field is numeric, but it is not. In turn, it is producing an Import Spreadsheet Errors table and not importing the remaining text values for this field. I have set the field to text in Excel but I still get the errors. I wanted to change the code so that I can specify the fields and their data types in Excel. Can this be done at all?

Thanks again for your help!!

[Mongo]

You can use TransferSpreadsheet method, but like you've found there are gotchas. Would it not be easier to create a link in Access to your Excel table, instead?

[NICOLE2000]

Mongo,

Thanks again for replying. Would you believe that after I linked the tables, Access still assigned data types to the fields despite the formatting in Excel? Upon reading another thread entitled "Access Or VB" I discovered that most users feel that Access is not the best tool to use for large databases. I have been feeling the sting from that bug for awhile now. So, my best bet would be to move the database consisting of a 6000 line table and a 12000 line table to an Oracle or SQL server as back ends. Thanks again for all your expediant help!!

[Mongo]

The wheels don't completely fly off Access until you cross the 50K record mark. It's MS' darned auto-correct jibberish that creates the nightmare here.

A couple thoughts...

Try sorting your offending Excel column(s) so the char values are upfront, then try importing again. Or, create the table structure you want in Access, then use ADO in a sub/function of your Access module to do the grunt insert work.

FWIW, thanks for the thanks :) There has seemed to be a shortage of returned kindness here lately, a word of thanks is much appreciated!

[Edited by Mongo on 07-12-2000 at 04:36 PM]

[Clunietp]

Get used to it, Mongo, there has always been a shortage on returned kindness