Access use Excel VB code?

[wockame]

am creating Access database using an Excel spreadsheet. would like to keep the code that highlights the row that i have marked DQ for DELINQUENT (colors row red =3) and PD for PAID (colors row yellow =6)

when i import my spreadsheet it does not show the highlighted rows.

am i needing to turn on a feature or something. would appreciate any help.

sadly,
wockame

 

[jcaparula]

Are you using Access 97 or Access 2000? I believe Access VBA modules in 97 or earlier were "independent" and could not share code with other Office apps.

 

[Bariloche]

wockame,

Because of the fundamental differences between spreadsheets and databases, Excel VBA and Access VBA are quite different.

In your case it appears to me that you are making the mistake that most, if not all, people make regarding databases and spreasheets; at least initially. And that is mistaking a database table in Access as being the same as a spreadsheet. You are probably thinking "after all, they look the same, so they should be handled the same." That's the problem though, they aren't the same. A database table just holds data. Queries, and their close "cousin", the Report, operate on the database table to give you some output, or result set.

What you need to do in order to "highlight" your DQ records is create a report. A report is where you can do the formatting you want. The unfortunate thing is that modifying a report format is not intuitively easy. It will take a little work. I'd recommend that you get a good Access book as a reference to help you through that process.

Don't take any of this to mean that I'm knocking Access. I'm not. I think its fantastic when used properly. But it isn't a spreadsheet program and users of it need to understand what its proper use is and how to get it to do what it was designed to do.


hth

 

[gocush]

Bariloche,
You seem to have a good working knowledge of databases and spreadsheets. Here's my Q
I have set up a "database" in an Excel worksheet (actually a series of columns with fieldnames as headers). This is in Workbook A.

I then close this workbook and use it as an External Data Source which I query using MSQuery. This returns a record set to Excel Workbook B. All's well so far.

Now I want to edit some of the records and return (upload or update) the records back into Workbook A. The HELP files say I need to check the "Allow Editing" command under the Records menu in Query. Problem is: the Allow Editing command is dimmed.

The HELP files give half a dozen cases where it is prohibited to add or edit records in the database; none seem to apply.

Further research has led me to the possibility of needing to use Dynamic Data Exchange DDE. ?????

If I can get this to work locally my goal is to publish Workbook A to the net and have users edit it from Workbook B after running a query.

Any ideas?
Paul

 

[Bariloche]

Paul,

I appreciate the complement, but, wouldn't you know it, I don't think I can help you much with your problem. I can duplicate the problem, but I don't know how to get around it. I'm thinking that its related to the fact that its an Excel table that's being edited. I can edit an Access table via MSQuery from Excel, but not the same table in Excel.

For your ultimate goal, though, I'm not sure that I'd want to go the route that you're taking. I don't think I'd want people to be able to directly edit my data table.

You mention "publishing ... to the net". Is that an intranet or the Internet? Regardless, I think you want to rethink how you're going to be collecting (or editing) your data. Its obvious right now that your current method isn't going to work. In a search of the NewsGroups I couldn't find anything about how to get around what you're experiencing.

You might want to approach this from the stand point of "how to collect data from remote users" rather than "Well, I know how to use MSQuery, so ...". The solution might be a shared workbook, or an Access form, or something that's HTML based. I don't know, like I said, I don't think I'm much help to you on this one. Sorry.

You might want to try someone like Tushar Mehta. He posts mainly on the Excel board. But I'm sure he has much more experience than I in this area.

Good luck

 

[gocush]

Bariloche,

Thanks for the input.

I have not been able to locate Tuchar Mehta running a search in this or the Excel forums. He must be using some other username. If you know it pls send it to me.

I have also received some relevent info which indicates that I should be able to have users update a database if the data is stored in an Access database. I know you have advised against this but that's the whole point I'm after: how can I allow specified users to query database records from xl, edit the recordset and then update the database.

For code on doing this from Excel to Access, see:
Excel reading data from Access

http://www.mrexcel.com/board/viewtopic.php?topic=33291&forum=2

I haven't tried this yet, but will.

Thanks again
Paul

 

[Bariloche]

Paul,

Mr. Mehta's "handle" on Mr. Excel is "tusharm." Or you can check out his site here. He may or may not be able to help. The reason I recommended him is that he is a rather knowledgable Excel user (and Access user, I believe) and he has a web site (so I'm assuming he is at least somewhat knowledgable about that sort of thing). I could be wrong though (about him being able to help).

Regarding the editing of Access data: I was able to execute a query in Excel using MSQuery that returned data into the query pane. I was then able to edit the data in a "cell" of that result "table" and the edit changed my Access data. This is the same functionality as you would experience in Access if you ran a straight "Select" query against a data table. If your query was a Total query (summing or counting data, for example) then it would not be "updatable" in this manner. I did not "return" the data to an Excel sheet. Since you mentioned the "Allow Editing" option I completely forgot about your mentioning that you wanted to return the records to a workbook.

My advice against allowing direct editing is because allowing direct editing of your data table opens up the possibility of invalid data being entered, individual records being deleted, or the entire data table being destroyed. If those "risks" are not applicable to your situation, or have been mitigated in some way, then my advice on that point can be ignored.

You may want to post a more complete description of exactly what you are trying to accomplish. I'm sure there's someone around here who can direct you as to how to get to the end you desire.


enjoy