MS Access Newsgroups lack real synergistic help

Arvin,

You have helped and are one of the "good ones."

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

2) Can anyone share a VBA macro to import a text file where there is code examples to rename or name
Field1 to "Whatever" and to set the data type?

I cannot believe that I cannot get useable answers I get long self aggrandizements which take far
more time than the question.

I guess I am a bit frustrated.

"Arvin Meyer [MVP]" wrote:

I see that the original question that you asked was answered incorrectly,
and I also see why you may be upset. Instead of venting your frustration,
please ask again.

At the end of the day have you made any positive progress in this Forum? I think not.

a a r o n _ k e m p f wrote:

if you've been using Access for that long-- you'd be using SQL Server
by now. I guarantee it.
Access is just a speed bump on the path towards SQL Server.

A pothole.
A driveway to the information super highway that is 'SQL Server'.

You're kidding yourself.
It is inconcievable that you have this much experience with Access and
yet you cannot spell SQL Server







On Jul 27, 5:45*pm, "Arvin Meyer [MVP]" wrote:
That's odd. I've been on these newsgroups since they started over 12 years
ago, and before that on the CompuServe Forums. This is my 8th year as an
Access MVP. Additionally, I operate the 3 Access websites in my sig. In all
that time, I thought I was providing exactly the kind of help that you seem
to be asking for.

I usually don't answer questions that I see have been answered by another
MVP, nor to I give the man a fish when he wants to learn how to fish. When I
point to a book, it's precisely because I know that the copyrighted code is
in there or because a book was the question. I point to a website when I or
someone else has written the code or an example, or when Microsoft has
written a KB article.

Because Access is a database, and a database is generally not a simple
thing, there is often no simple answer, or the answer may not be what you
want to hear. I usually won't give you code to do something that I know is
wrong.

So specifically, what is it that you are having a problem with?
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

wrote in message

...



Over the years I have noted a significant difference between the XL and
the Access NG's

MS would be smart to suggest to its Access MVP's to teach both by
encouraging additional self
research coupled with real substantive immediate help.

i.e. if I ask:

Would someone supply a textfile import macro that I could edit? *Or let me
know how to "get to" the
saved import steps on 2007 for use in 2003?

or

I need the VBA code to delete the first three records (headers from a text
import).

* * * DoCmd.RunSQL "DELETE ???????

These answers are simple to Access professionals.

Sure links to additional sources are great. *But let us be real. *If you
went to a more knowledgable
person and asked a question that you knew he knew and he pointed to 5 1000
page books how would you
really feel?

Sometimes a simple answer to a simple question works. *Access needs new
business and for others to
catch the creativity-motivation curve to solve immediate issues.

In short, answer questions with solutions not just additional projects
which distinguishes
motivation to use Access.

I realize that I may never get help again in Access NG's because of the
hissy-fits which will
follow.

Stimulate! Encourage! Create! Help!

EagleOne

On Sun, 27 Jul 2008 21:19:34 -0400,
wrote:

Arvin,

You have helped and are one of the "good ones."

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

If you can be CERTAIN that the order of records in the recordset is
stable (which you cannot, if it's a JET or SQL Table, though a linked
text file should be ok), you can open a Recordset based on the linked
text file and use code like the following (adapted as necessary, this
will delete the first three records):

rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Note that this requires an updateable recordset and it will
permanently delete the record from the linked file.

You may do better to use the OpenFile method in VBA to open the
external file, as a file, and parse it into your table directly. It
depends on how the file is structured.

2) Can anyone share a VBA macro to import a text file where there is code examples to rename or name
Field1 to "Whatever" and to set the data type?

One way would be to link to the file and create an Append query such
as

INSERT INTO localtable (FieldA, FieldB, Whatever, This, That,
Theother)
SELECT Field1, Field2, Something, SomethingElse, Field5, Field6) FROM
linkedtable;

I cannot believe that I cannot get useable answers I get long self aggrandizements which take far
more time than the question.

I guess I am a bit frustrated.

"Arvin Meyer [MVP]" wrote:

I see that the original question that you asked was answered incorrectly,
and I also see why you may be upset. Instead of venting your frustration,
please ask again.

--

John W. Vinson/MVP

wrote:
Arvin,

You have helped and are one of the "good ones."

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

There is no such thing as record #1, or record #2 in a database unless you
define what you mean by those descriptions. The very question highly
suggests someone that doesn't know what they are doing. You are then
surprised if you get requests for clarification rather than explicit
instructions?

2) Can anyone share a VBA macro to import a text file where there is
code examples to rename or name Field1 to "Whatever" and to set the
data type?

I for one have no idea about anything in this question. "VBA Macro" does
not compute. Most MVPs have little or no experience with macros (I
certainly don't). Renaming fields via code is not something that would
often be done. Neither is setting the data type. Again the question highly
suggests that questionable actions are being performed so inquiring "why?"
is a perfectly natural thing to do.

I cannot believe that I cannot get useable answers I get long self
aggrandizements which take far more time than the question.

I guess I am a bit frustrated.

Despite the level of expertise in here, not every answer is a matter of
pounding the keyboard. Some (like yours) would require opening a test file
and actually working out an example. At least if the one posting the answer
wanted to be sure it would be correct. You expect someone to do that much
work when the desired action is questionable in the first place?

You get less of this in other types of groups because other software areas
do not have these types of issues. Try spending some time in the HTML or
javascript groups. The heavy responders in those groups make a regular
habit of tearing to shreds any post that even remotely suggests that the
poster is a rookie or hasn't done their homework prior to posting. I have
never seen more considerate and helpful responders (as a group) than what is
found in the Access groups.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

wrote in message
...
Arvin,

You have helped and are one of the "good ones."

I think yhat you'll find most if not all of the MVPs here are more than
willing to help.

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

There really isn't any, since you can't really ever be sure what the first 3
rows will be. John Vinson pointed out one answer, but as he also mentioned
you can't really be sure. My suggestion is to use some criteria to determine
what data that those 3 rows will have, and use that in a Where Clause. If
you can provide some table names and the fields that contain the data to be
deleted, and the data criteria, I can write the code for you.

2) Can anyone share a VBA macro to import a text file where there is code
examples to rename or name
Field1 to "Whatever" and to set the data type?

What you want is the "TransferText Method" Here is the syntax:

DoCmd.TransferText [transfertype][, specificationname], tablename,
filename[, hasfieldnames]

The helpfiles should have an example.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I cannot believe that I cannot get useable answers I get long self
aggrandizements which take far
more time than the question.

I guess I am a bit frustrated.

"Arvin Meyer [MVP]" wrote:

I see that the original question that you asked was answered incorrectly,
and I also see why you may be upset. Instead of venting your frustration,
please ask again.

Folks,

What I learned today in this forum is that I spent good money for Access when I should have
purchased SQL server. This is an Access NG correct?

I am obviously not a significant Access user. That said, what I am now working on is an idea which
will cause a significant improvement in information and decrease time in account reconciliation's.

I do not have the time right now to become an Access guru. My questions were very simple and
answers were needed before tomorrow I had hoped.

The mission of this forum s/b to encourage Access use and proliferation.

I do not have PC-level access in my organization to SQL Server. More, I am sure that the 10,000+
Access users in my organization would not be happy campers if they knew that MS was badmouthing
there own product on their own forum - not real encouraging.

Access is what we have and must use. I was just looking for some real help.

Someone above has given answers

"Rick Brandt" wrote:

wrote:
Arvin,

You have helped and are one of the "good ones."

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

There is no such thing as record #1, or record #2 in a database unless you
define what you mean by those descriptions. The very question highly
suggests someone that doesn't know what they are doing. You are then
surprised if you get requests for clarification rather than explicit
instructions?

2) Can anyone share a VBA macro to import a text file where there is
code examples to rename or name Field1 to "Whatever" and to set the
data type?

I for one have no idea about anything in this question. "VBA Macro" does
not compute. Most MVPs have little or no experience with macros (I
certainly don't). Renaming fields via code is not something that would
often be done. Neither is setting the data type. Again the question highly
suggests that questionable actions are being performed so inquiring "why?"
is a perfectly natural thing to do.

I cannot believe that I cannot get useable answers I get long self
aggrandizements which take far more time than the question.

I guess I am a bit frustrated.

Despite the level of expertise in here, not every answer is a matter of
pounding the keyboard. Some (like yours) would require opening a test file
and actually working out an example. At least if the one posting the answer
wanted to be sure it would be correct. You expect someone to do that much
work when the desired action is questionable in the first place?

You get less of this in other types of groups because other software areas
do not have these types of issues. Try spending some time in the HTML or
javascript groups. The heavy responders in those groups make a regular
habit of tearing to shreds any post that even remotely suggests that the
poster is a rookie or hasn't done their homework prior to posting. I have
never seen more considerate and helpful responders (as a group) than what is
found in the Access groups.

Thank you very much!

John W. Vinson/MVP wrote:

On Sun, 27 Jul 2008 21:19:34 -0400,
wrote:

Arvin,

You have helped and are one of the "good ones."

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

If you can be CERTAIN that the order of records in the recordset is
stable (which you cannot, if it's a JET or SQL Table, though a linked
text file should be ok), you can open a Recordset based on the linked
text file and use code like the following (adapted as necessary, this
will delete the first three records):

rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Note that this requires an updateable recordset and it will
permanently delete the record from the linked file.

You may do better to use the OpenFile method in VBA to open the
external file, as a file, and parse it into your table directly. It
depends on how the file is structured.

2) Can anyone share a VBA macro to import a text file where there is code examples to rename or name
Field1 to "Whatever" and to set the data type?

One way would be to link to the file and create an Append query such
as

INSERT INTO localtable (FieldA, FieldB, Whatever, This, That,
Theother)
SELECT Field1, Field2, Something, SomethingElse, Field5, Field6) FROM
linkedtable;

I cannot believe that I cannot get useable answers I get long self aggrandizements which take far
more time than the question.

I guess I am a bit frustrated.

"Arvin Meyer [MVP]" wrote:

I see that the original question that you asked was answered incorrectly,
and I also see why you may be upset. Instead of venting your frustration,
please ask again.

Slow down, take a deep breath and read and learn. Keep a sense of
humor if you can. :-)

Remember, you have the last word. There is an unconditional guarantee
of satisfaction or your money back!

The people who respond to the posts here are unpaid volunteers. A
great first step would be to visit www.mvps.org/access and find and
read "netiquette". Yes, you have transgressed but that's not the
point (you're forgiven this first time). The article describes the
group charter in general and lays out some of the many behavioral
expectations. They work well for the community so we ask that all who
partake of our services try to honor the spirit of what you read
there. I'm not the hall monitor. Microsoft provides the site but
nobody's in charge. Most importantly, nobody has to do anything for
anybody. Many people's names do, indeed, end up in many killfiles.
Courtesy and consideration go a long way toward getting a problem
solved.

I've been lurking these Access newsgroups and responding since
versions 1.0 and 1.1. One of the nest prolific responders in those
days was Dev Ashish who put up the Access Web. If you do indeed plan
to develop using MS Access then it's one of the most valuable
resources you'll find.

What you've already read from other responders is true: In order to
understand and make proper use of Access a developer must understand
Relational Database theory and how to use the whole host of developer
goodies in Access. It is orders of magnitude more difficult to master
than Excel. FWIW over many releases of MS Office my judgement is that
Excel is the leader in technical excellence at every release.

Your statement that VBA in Access is different from VBA in Excel is
incorrect. Actually, the underlying VBA engine is exactly the same
but the Object Model and platform specific methods and properties
change with the platform. I refer you to Microsoft themselves for
that. Also the VBA Developer's Handbook by Getz et alia.

Without context and some means of identification, it is impossible to
know that the first three records found in a recordset based on a
table are, in fact, the ones you want. Data in tables is in an
unordered heap. You can use Queries to place it in a specific order.
So, "The answers are *not* simple to Access professionals" without
context.

If you plan to become an expert Access developer then, in time, you
will feel qualified to give accurate answers to other people's issues
(please don't "guess"). If that's the case then welcome aboard. In
time maybe you'll provide the level of service to others that you now
seek.

Many of the journeyman and expert developers here including many MVPs
are self taught. We learned by lurking the newsgroups, reading all
relevant books, subscribing to periodicals and, most importantly,
studying the Help files. Most of the MVPs attend conferences
sponsored by Microsoft on their behalf.

HTH
--
-Larry-
--

wrote in message
...


Over the years I have noted a significant difference between the XL
and the Access NG's

MS would be smart to suggest to its Access MVP's to teach both by
encouraging additional self
research coupled with real substantive immediate help.

i.e. if I ask:

Would someone supply a textfile import macro that I could edit? Or
let me know how to "get to" the
saved import steps on 2007 for use in 2003?

or

I need the VBA code to delete the first three records (headers from
a text import).

DoCmd.RunSQL "DELETE ???????

These answers are simple to Access professionals.

Sure links to additional sources are great. But let us be real. If
you went to a more knowledgable
person and asked a question that you knew he knew and he pointed to
5 1000 page books how would you
really feel?

Sometimes a simple answer to a simple question works. Access needs
new business and for others to
catch the creativity-motivation curve to solve immediate issues.

In short, answer questions with solutions not just additional
projects which distinguishes
motivation to use Access.

I realize that I may never get help again in Access NG's because of
the hissy-fits which will
follow.

Stimulate! Encourage! Create! Help!

EagleOne

John,

There are two tables. They are not linked. In theory they should contain identical information -
but they do not. I need to just "union" the tables and ID the variant information.

There are 22 fields and my WHERE clause will include 5 of those.

I need to import the information via text file. I cannot use Excel because of the record limits.

I cannot use the Main Frame headers but they are imported via the Text import wizard. I want to
delete them. Using VBA, I wish to rename the Fields and set the data type.

This is not rocket science. Thank you so much for your time and knowledge.

John W. Vinson/MVP wrote:

On Sun, 27 Jul 2008 21:19:34 -0400,
wrote:

Arvin,

You have helped and are one of the "good ones."

Where, pray tell, was an answer? There is too much smoke.

Actually, my comments were to challenge not to provoke.

1) What is the VBA command to delete record #1, or #2 etc

If you can be CERTAIN that the order of records in the recordset is
stable (which you cannot, if it's a JET or SQL Table, though a linked
text file should be ok), you can open a Recordset based on the linked
text file and use code like the following (adapted as necessary, this
will delete the first three records):

rs.MoveFirst
rs.Delete
rs.Delete
rs.Delete

Note that this requires an updateable recordset and it will
permanently delete the record from the linked file.

You may do better to use the OpenFile method in VBA to open the
external file, as a file, and parse it into your table directly. It
depends on how the file is structured.

2) Can anyone share a VBA macro to import a text file where there is code examples to rename or name
Field1 to "Whatever" and to set the data type?

One way would be to link to the file and create an Append query such
as

INSERT INTO localtable (FieldA, FieldB, Whatever, This, That,
Theother)
SELECT Field1, Field2, Something, SomethingElse, Field5, Field6) FROM
linkedtable;

I cannot believe that I cannot get useable answers I get long self aggrandizements which take far
more time than the question.

I guess I am a bit frustrated.

"Arvin Meyer [MVP]" wrote:

I see that the original question that you asked was answered incorrectly,
and I also see why you may be upset. Instead of venting your frustration,
please ask again.

wrote

Interesting! My point still stands.

Not one MVP answered my questions.

All just pontificated and rationalized there indefensible positions.

First, you should be aware that not everyone who responds here is an MVP.
aaron kempf, for example, is not an MVP, nor does he often provide useful,
or even serious, responses to questions about Access. He appears to be, for
some reason, mentally pre-programmed to respond to all Access questions with
"move to SQL Server" whether or not that is an appropriate or useful answer.

Is it "pontificating" to explain that sometimes there is no "easy, simple
answer" that someone can just type in from memory? (See my comments below
regarding File IO.)

Is there some reason why John Vinson's approach does not work for you? Link
the external text file, open it as a Recordset, and try his code. Looks to
me to be worth a try.

If it doesn't work, post back with some details about the text file (fixed
field or delimited or ???) and someone can likely offer you a FileIO
solution -- as I don't do FileIO very often, any more, and as I don't have a
lot of time and energy to create a new example or to go search out an
example and revise it for your purposes, I might very well give you a link
or a reference to a resource. (Most of the VBA FileIO is not a great deal
different from the File IO from previous versions of BASIC, including the
DOS BASIC interpreters that came bundled with MS DOS and early versions of
Windows.)

I'm sorry you are unhappy with the level of response you are getting here in
microsoft.public.access; you might visit the USENET newsgroup
comp.databases.ms-access. Not nearly as many MVPs post there, anymore, but
there are still a lot of useful answers.

Larry Linson
Microsoft Office Access MVP