Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to Excel (97, 2000, and 2002)

    Could someone explain what LinkCriteria does in a sub procedure and what to look for if there are problems?

    I received an Access Database and Excel report from an overseeing agency. The first version was in 97, it worked but spurious data was displayed in Excel. The second version was supposed to be 2000, but they sent the wrong Excel report and just resent the 97 version. It doesn't work at all. When I try to convert the 97 version to 2000, it comes back with the VBA application is corrupted. The database is confusing: not normalized, mostly macros, no relationships, field names like "Date", and memo fields in nine tables that should be one, each of these tables repeats the fields "reviewer", "provider", and "chart number" and that information is entered using macros when ever a command button is clicked, a value list for the "reviewer" field that I can't update, and finally a table for "provider" but no link to it that I can find. The data from each table is sent over to Excel and all the queries are done there. Worst of all, my boss won't let me to post it here, so you can look at it.

    As best I can tell, the problem is with the data links. The macros copying the data into the tables are working, but I can't get Excel to read it. If I make the database more normalized, one table would have 137 fields plus 9 memo fields. I would be expected to keep the forms as they are. Right now, I would be happy just figuring out how to make the links work. I don't know what to look for to even start to figure out the link problem.

    Here is one of the sub procedures:
    Private Sub Command71_Click()
    On Error GoTo Err_Command71_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Intake_Eval_Comp"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command71_Click:
    Exit Sub

    Err_Command71_Click:
    MsgBox Err.Description
    Resume Exit_Command71_Click

    End Sub

    Any help would be appreciated? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (97, 2000, and 2002)

    The code you posted has nothing to do with a link to Excel. It is the On Click event procedure for a command button named Command71 (ugh) and it opens a form named Intake_Eval_Comp. It is standard code generated by the Command Button wizard; the wizard always declares variables stDocName (the name of the form or report to be opened) and stLinkCriteria (an optional Where-condition to filter the form or report). In this code, stLinkCriteria isn't used at all.

    If the export to Excel is done from the database, it could be done in macros,in which case you have to look at their names and their design to find out which ones, or in VBA code; do a search for DoCmd.TransferSpreadsheet an/or for DoCmd.OutputTo (with acFormatXLS as one of its arguments) to find out where.

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (97, 2000, and 2002)

    Hans, thank you for the prompt response. There are no macros sending the data to Excel. They do have a command button (#9) that sends delimited data to the A drive with a file for each table with review data in it (nine of them), but if I convert them to Excel they don't match up with the appropriate worksheet. What I was told is that Excel would get the data from the drive if both the database and Excel report were in the same drive and folder, which they are. Don't know If the problem is the fact we have Excel 2002 versus 97 or 2000.

    I don't have to worry about prior data in the fields, so keeping the forms looking the same, and linkage to Excel are the only important parts. The database has a command button (#5) to delete the data in the nine tables before using it for the next review. It might be easier just making the database more normalized and then sending the data over to Excel. I'm concerned about the nine memo fields. There have been enough comments on this Lounge about memo fields to concern me. Based on my experience with prior chart reviews, they tend not to enter data in the memo fields often. If I do put all the memos fields in there, will that create problems? We probably won't have more than 70 records in the main table. Unfortunately, I'm not a programmer by nature and plagiarism is the only technique I have for generating code. My main goal in reading Woody Watch posts is to have a decent enough database, so that if a real programmer has to do something it won't be a huge hassle.

  4. #4
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (97, 2000, and 2002)

    The memo fields aren't transferred. Mainly they are used for notes when a conflict with the entity being evaluated arises.

    Thanks for the help. Will email if I have problems changing the database to something that makes a bit more sense.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (97, 2000, and 2002)

    I mentioned in another recent post (<post#=255713>post 255713</post#>) that the actual experiences people have had with memo fields are varying. I have had very few problems with them, others have had their databases corrupted beyond recovery. But if you're going to restructure the database anyway, you might investigate whether it makes sense to divide the memo fields into smaller parts, since the data will be exported to
    Excel. Although a cell in Excel can contain up to 32,767 characters, I believe, only the first 1,024 are actually displayed in the cell.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access to Excel (97, 2000, and 2002)

    Expanding on the note by Hans, there are some issues in exporting memo fields to Excel - I believe that in general the cell will be truncated at 255 characters. It seems to me that Excel 97 was limited to 255 characters in a cell - I'm not sure where it changed, as the only thing I have accessible at the moment is the 2002 version. But if you aren't transferring them, then it's no big deal.
    Wendell

  7. #7
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (97, 2000, and 2002)

    I need more help, please. The database had another nine tables with four fields: Seq, Number, Reference, and Standard. They refer to the government regulations governing the inspection criteria. I've made one table, tblWAC, with the fields WACID, WACNumber, WACReference, and WACStandard and appended the nine into it. There are nine inspection categories and each has it own form accessed through a command button on the main form and I have to keep both. In each of these forms is a subform that is tied to the regulation tables. I don't know the best way to query tblWAC to only see the appropriate regulations. The text field WACID has two letters and then two numbers to keep the sequence (i.e. Community Support 2nd reference would be CS02).
    Thank you for all the help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (97, 2000, and 2002)

    Is there a specific reason you want to use 9 different forms? It would seem that they are all the same, but display a different subset of the data, or am I wrong here?

  9. #9
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (97, 2000, and 2002)

    Hans,
    I do wish I could send the database, but no luck. Actually the nine forms that the regulation subform would show up on are all different. There are 137 criteria and the people doing the evaluations think of these things as part of nine separate categories. They want to see them in their categories and do want to see the regulations tied to them. I haven't even enjoyed putting in fake stuff to try out the original database, but the person who will be using it wants it the way it is.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (97, 2000, and 2002)

    If it is still possible, I would change the structure of tblWAC slightly. You mentioned that you have created a field WACID consisting of two letters identifying the category and a two digit sequence number. I would split it into a Category field (text, length 2) and a Seq field (numeric, integer or long integer). These two fields together would form the primary key in this table. To select the records for a specific category, simply set the criteria for Category to the two letter code. You can order the records within the category by Seq.

  11. #11
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (97, 2000, and 2002)

    Will do the change. I thought about categories, but tend not to think for two fields for the primary key.

    As I beat my head against the wall doing this, I keep daydreaming of Charlotte chatting with the designer of this database. I'm not sure she would waste her time on him, but I would enjoy it. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •