DoCmd.TransferSpreadsheet does not export all fields

Moniker

I am well aware you can make a number of tables look and act like one table as I do it all the time.

But when you have 11 tables involved about the only thing you would achieve is making it slower. Actually I have never tried to join 11 tables in a query.

The other issue is I am not trying to display the data from all of the persons other "Many" tables. I just want the last record. Also, if I combine all the tables then I will turn a few 1000 records into countless thousands. If the query is set so only the last record from each of the Many tables is shown then that can only slow things down.
 
Bob

I will tell you at least one area where there is an exception.


Mike

The exception only occurs in your mind and in your un-normalised database, please stop telling newbies (or anyone else for that matter) that it's acceptable:rolleyes:
 
Rich,

As usual you never offer a solution.

On my thread about combining the contents of memo field records with an alternative to SendKeys, your only answer...was why display it that way. My bookmark solution might not be the best but it works and allows the display to be done without SendKeys.

I have a thread up on normalising. How far do you go....as I am helping someone make a data base and it is a "start from scratch". But that would be too difficult for you as it is about real fields etc.

As to the above situation I think combining 11 tables and at the same time and only having the most recent record from the "many tables" will only slow things down.

Maybe someone is reading this thread or the other threads and all are doing is demonstrating that there is no solution via your methods.
 
However, "a solution" is not always the right answer. Someone can be out of gas on the side of the road and call me for help. "A solution" is to tell that person to push their car. It works, but it's not the best solution.

The idea is not necessarily to provide "a solution", but to teach someone how to get to the solution. There's not a good reason to provide a solution if someone doesn't understand the problems in the first place. Usually, it would be pretty easy to provide a solution to most of the questions asked in here, but this is the "give a man a fish" versus "teach a man to fish" method of helping.

With the problem that has been posed in this particular thread, there is no good solution as you flat out cannot output more than 255 columns to Excel, unless you're in Excel 2007. Additionally, as myself, Bob, and others have pointed out, a single table with that many fields is just asking for major headaches down the road. You might be able to eke out a little more functionality, but eventually, the entire thing is going to become a maintainence nightmare and will have to be scrapped. In this case, the better suggestion is to step back and ask yourself, "Why do I have 440 fields in one table?"

Judging by the responses from the original person that started this thread, he's a little lost with normalization and he's just looking for a "quick fix". That's exactly what you don't want to do most of the time. If he doesn't understand the problem in the first place, the solution is meaningless, and you'll end up answering every question he has along the way. If he understands both the problem and the solution, chances are much greater that he will be able to answer his own questions and perhaps other people's questions in the future.

This is the Access Forum, not the Access 101 Classroom. This is where you post when you're almost over the hump, but need that little nudge in the right direction. When that nudge becomes a multi-person push (think of the car analogy), then it's fairly clear that the person asking the question needs to better educate himself on what the problem is in the first place.

With that said, I fully realize that sometimes, the people asking questions basically have a database dumped on them without warning, and they are left to swim for their lives. It's not a fun situation to be in, to be sure, but that person has a few options. One, they can flatout say to management, "I'm not sure if this project is in the scope of my current abilities" or something similar. Two, they can crash course themselves in Access. Three, they can fudge their way through it by asking people like us to basically write their code for them, not learn anything from it, and then be screwed down the line when management thinks they are capable of such tasks, when things break, when enhancements are needed, etc.

A simple look at the "Right Way To Ask Questions" post that is stickied at the top of the General section of this forum will show that what I am describing is accurate. A rather large percentage of the people fishing for answers here have never read that, but it essentially states, "You'd better exhaust every option you have before asking a question, and if you're still stuck, post a clear, concise example of what you need help with. Include design or code samples, explain error messages you get and where they happen, etc."

This rarely happens, but look how much the veterans of this place jump all over those posts with good responses when it does happen. This is also why many of the veterans of this place respond with, "Why ever do X that way?" or "Have you tried to accomplish X using Y?" or "Search for X for help on Y" instead of, "Here's your exact answer which probably won't make a lot of sense to you, but you won't ask about what it means if it works for you."

In short, a nudge in the right direction is often the better solution rather than just flat out handing the person a crib sheet. "Yeah, I got a A on that test, but I cheated and don't understand what I answered" is far worse than "I got a B- on that test, but I know what I messed up and with a little more studying, I'll do better next time around."
 
Rich,

As usual you never offer a solution.
.

As the only member of this forum who drove Pat Hartman to swear that's a little rich, don't you think. You've been told umpteen times to normalise your db and offered examples, instead you choose to keep trying to justify its design. Best of luck
 
I simply state why I do it a certain way and if it can be changed (for gain) then I will do it. But usually it is just a bunch of loose statements that are forthcoming.

Bob said it was extremely rare that a table should have 200 plus fields. I said I have one and the reason why......which is to satisfy telemarketing requirements I have.....Now if someone tells me that I will lose nothing by combining one table with the most recent record from about 10 tables, then I will do it, assuming I gain. I have reservations about how it would work but no one has said....no problems there. I would assume other readers, like myself, can only think the alternative to what I am doing with the table is not a goer.

You say normalise the big table. Well that is the case because of the other "many" tables. But I must have the data from the latest record of each of those tables in either one table or a join of 11 tables and joing must only include the most recent record.
 
You're at it again:rolleyes:

You are the one who is at it again. If it was not for me all your recent postings would be restricted to the political/watercooler forum.

I should not be this important in your life.
 
"a solution" is not always the right answer. Someone can be out of gas on the side of the road and call me for help. "A solution" is to tell that person to push their car. It works, but it's not the best solution.
Well Moniker, it's pretty noble to keep the "hope" alive, but as the last few thousand years have proven, people are people, and the ignorance never stops. Although, as far as I'm concerned, you're welcome to keep fighting it. :) Maybe you'll stop it someday.
The idea is not necessarily to provide "a solution", but to teach someone how to get to the solution.
Boy, aint that the truth? But maybe that's not what the people want? I have my doubts. Maybe they're happy with the "PatchWork" method until things completely fall apart? Let us not forget to not underestimate the power of people to be stupid. :rolleyes:

(As for Rich and Mike, how about staying off the pedestals on this one guys? We don't need another 10 pages of bickering ;))
 
Mike,

I think the problem is that you can't visualize how that structure would look normalized, and therefore you're attempting to justify a denormalized structure because it makes sense to you. That's fine, but it's not a particularly good solution, and in fact it's far slower than a properly normalized DB with multiple table joins.

I can understand what you're saying -- it's pretty much the "if it ain't broke, don't fix it" mentality -- but it's asking for issues in the future. Maybe next week, maybe next month, maybe next year -- there's no way to tell when. But it's a house of cards, and each time you add another card to the top, the whole thing is becoming more and more unstable, regardless of whether or not you realize it.

Rich is right in that you're making excuses for the existing structure rather than transistioning to a more stable foundation. This is all just my opinion, of course, and you'll probably continue down the path you're on. Just know that, eventually, it's going to collapse. If you start working on a better solution while the old one is still working, you'll be that much ahead of the curve.
 
Moniker

It is not a case of making excuses but simple statements of why I have something done a certain way. I don't say it is the best but it is the best available to me.

Because this data base is about 11 years old it has plenty of crappy stuff that should be changed but it comes back to effort in....result out. Actually, last Obtober I was going to change a problem it is has with daylight saving. Australia does not have daylight saving in all states and so the time difference alters at October and March. Because that part was made very early on it requires me to physically change a number in about a dozen spots.:D

With the big table what Rich is failing to grasp (or acknowledge) is the data is stored in One to Many....You say combine the 11 tables but I have effectively done that and you might say did as a MakeTable query.

At the moment that section of the data base is 1 table plus 10 tables. If I change it will be 1 table plus 10 tables plus a very flash query.

By the way,all I need to do to the big table is go to Design and run down the selectors and delete the bottom 80%. OK, lets say I do that and join it to the other 10 tables. Remember the join can only include the most recent record from each of the 10 tables. How will that query compare for speed to the single table?

The advantage I would see is that currently data goes to a Many table but in addition goes to the fields in the big table and does it with SetValue. However that occurs in an instant so I suppose there would have only been half as many actions when it was first made. But even that is small because it was copy/paste/change form name in macro actions.

So at the moment (but I am open to how to improve that part of the data base) I have the "one to many" and I feel that my requirment to be able to have every recent record from the other 10 tables on the same record is best (speed) with a table as opposed to a query to join the 11 tables.

One reason I do some things is to get it made now. Adam will testify that a thing I have for the diary system could be waiting for a long time if it was made "according to the rule book" (how are you going with it Adam):D

When macros can be used I prefer them for my situation. Obviously there are some things that require code, meshing with Word being my key one. I like macros because when I need to do something I can quickly look at the macros I have and pick one that is already close to what I want. Perhaps you can do that with code on forms, but if so I don't know how to see it.

Another advantage to me of macros is that I send them to someone else. With code they will stuff it up. I can't send the form because we use forms that are different..appearance etc.

The other reason I like macros is because I can be on the phone with a telemarket or insurance salesman and we can change a macro while the data base is full open.

Now perhaps you can do all that with code. If so then I will change.

Mike
 
You've been posting the same crap here for years, just how much longer do you intend to bore the crap out of us with it?:rolleyes:
 
hmmm...for YEARS? He's only got 552 comments, and I sure hope some of them were useful. :rolleyes:
 
Well Rich,

I guess I don't believe you.

hey BTW, do you even work? I don't think I've ever seen a post of yours that has anything to do with the program we work with. Are you here because you have no job? (No offense man, but that's what it seems like). ;) (I'm semi-serious here, so how about an answer?)
 
Well Rich,

I guess I don't believe you.

hey BTW, do you even work? I don't think I've ever seen a post of yours that has anything to do with the program we work with. Are you here because you have no job? (No offense man, but that's what it seems like). ;) (I'm semi-serious here, so how about an answer?)

My first thought was just to ignore your response however I suggest you search the archives, or those that are left after the site was hacked. With the exception of Pat Hartman I've been here longer than anyone else. As for not answering many posts these days is the very fact that I work for a living:rolleyes:
Oh here's one that survived the hack, I suggest you read it and then post back and tell me Mike375 isn't wrong
http://www.access-programmers.co.uk/forums/showthread.php?p=286619#post286619
 
Deleted...No point in assisting Rich in turning the Access forums into the Political forum
 
Here is your hero saying in the usual dogmatic fashion, what can't be done. Good thing I did not take the "advice" and got someone who knew what they were doing.

http://www.access-programmers.co.uk/forums/showthread.php?t=69069

Got hand it to you Rich...you have been able to transfer the shit from Political up to the Access forums.

Here you are again, the likes of PH and SJ etc are all wrong and your DB is brilliant, carry on but don't come back and brag about this rubbish and even worse argue its merits here any longer
 
Deleted...No point in assisting Rich in turning the Access forums into the Political forum

Your nonsence has nothing to do with politics but correct programming practices which you've chosen to ignore and impress us with
 

Users who are viewing this thread

Back
Top Bottom