File Save problems

[Cipher via AccessMonster.com]

I have a text file that is read into a Table. The file is not linked as I
wish to alter/add records and record data. I have generated a FORM that reads
the Table so that the USER can alter the records. Here is the problem:

Microsoft forces me to use FORMATTED text data on the EXPORT and, exports to
an additional file with a FORM suffix to the file name. I did not ask Access
to do this but this kind of Big Daddy attitude of Microsoft is quite typical..
.everybody knows Microsoft knows much more about our business than we do and
frequently forces us to accept situations.

Here is what I need help with:
How can I force Access to write the altered data within the FORM back to the
original text file WITHOUT changing the format in any way...thanks all.

 

[John W. Vinson]

I have a text file that is read into a Table. The file is not linked as I
wish to alter/add records and record data. I have generated a FORM that reads
the Table so that the USER can alter the records. Here is the problem:

You must indeed import the data to have it editable, as a text file on disk
cannot be indexed, cannot support referential integrity, and cannot be edited
directly from Access.

Microsoft forces me to use FORMATTED text data on the EXPORT and, exports to
an additional file with a FORM suffix to the file name. I did not ask Access
to do this but this kind of Big Daddy attitude of Microsoft is quite typical..
everybody knows Microsoft knows much more about our business than we do and
frequently forces us to accept situations.

This of course depends on how you're exporting the data. The TransferText
method in VBA code exports data from a table (not from a form, which it cannot
do since a Form is just a window, not a data storage medium); it does not put
any constraints on the filename, and certainly does NOT use the word FORM as
part of the filename. Don't blame Access if you choose to use the wrong tool
within the program!

Here is what I need help with:
How can I force Access to write the altered data within the FORM back to the
original text file WITHOUT changing the format in any way...thanks all.

Use TransferText and an output specification which matches your file
structure.

 

[Cipher via AccessMonster.com]

If I import data into a Table and then use a FORM to access this information,
it is possible for me to ADD, DELETE and CHANGE data with the Form. I can
export data from the Form into a Text File, but ACCESS refuses to write the
data back into the original text file. It will, however, write another text
file by appending the word 'Form' to the original name of the file and then
builds stupid '---' around the data. So, it IS possible for the Form to wride
the data back to a test file, unfortunatly, Microsoft felt that adding stupid
DASHES around the data would (somehow) make things much better.

So it is not me using the wrong tools it is Microsoft having no ability
whatsoever in the design of a Database Application. If Microsoft enabled me
to UNCHECK the check box specifying "Formatted Data on Export" things would
be fine.

Now I have to learn VBA because Microsoft is too laim...thanks for the
information.

 

[John W. Vinson]

If I import data into a Table and then use a FORM to access this information,
it is possible for me to ADD, DELETE and CHANGE data with the Form. I can
export data from the Form into a Text File,

No. You *cannot* export data from a Form into a text file, since forms do not
contain any data. You can export data FROM A TABLE (bound to a form or not, it
makes no difference) into a text file. The export wizard on the form is in
fact exporting data from a Table, not from the form.

but ACCESS refuses to write the
data back into the original text file. It will, however, write another text
file by appending the word 'Form' to the original name of the file and then
builds stupid '---' around the data. So, it IS possible for the Form to wride
the data back to a test file, unfortunatly, Microsoft felt that adding stupid
DASHES around the data would (somehow) make things much better.

Yep, the wizards do some pretty silly things.

So it is not me using the wrong tools it is Microsoft having no ability
whatsoever in the design of a Database Application. If Microsoft enabled me
to UNCHECK the check box specifying "Formatted Data on Export" things would
be fine.

Now I have to learn VBA because Microsoft is too laim...thanks for the
information.

VBA *IS* the means by which Access enables the design of a database
application. That's what its name means - Visual Basic for Applications. I'm
sorry you feel that the end-user wizards should have been designed to fit your
specific requirements and that no learning on your part should be required!

 

[Dirk Goldgar]

I have a text file that is read into a Table. The file is not linked as I
wish to alter/add records and record data. I have generated a FORM that
reads
the Table so that the USER can alter the records. Here is the problem:

Microsoft forces me to use FORMATTED text data on the EXPORT and, exports
to
an additional file with a FORM suffix to the file name. I did not ask
Access
to do this but this kind of Big Daddy attitude of Microsoft is quite
typical..
everybody knows Microsoft knows much more about our business than we do
and
frequently forces us to accept situations.

Here is what I need help with:
How can I force Access to write the altered data within the FORM back to
the
original text file WITHOUT changing the format in any way...thanks all.

There is *no* program that can edit a text file in place, unless the
modified data to be stored is exactly the same size as the original text.
It is possible to directly overwrite an entire file with a new or modified
file, and a text-editing program, which Microsoft Access is not, may do
that, or it may make an effort to protect against the consequences of
possible failure by actually saving a new copy of the file, then renaming
the old copy, then renaming the new copy to the old name, and then deleting
the old copy.

Access is not a text editor. As a database program, it is designed to edit
structured data in tables. In addition, it provides mechanisms to import
and export data in various formats including text, so long as that data is
tabular in structure. Access is perfectly capable of importing a (tabular)
text file, editing it as a table, and then exporting it to a text file
again. YOu can even tell it to use the same name as the original text file
and overwrite that file.

However, these caveats apply:

1. If multiple people are going to be editing the imported data and
re-exporting it to the original text file, they have to be doing their edits
in the same Access database. You can't have person A import the data into
one database, and person B import it into another database, have them edit
the imported data, and then export it back to the same text file. If you
do, one user's version of the data will overwrite the other's version.

2. When exporting the data to a text file again, it may require some
tinkering to get the data into the exact same text format as it was
originally input. That is because Access is data-oriented, not
format-oriented.

Your message makes it clear that you do not know how to export your data
properly, and you are using the incorrect method to go about it. It's as
though you were trying to drive in a nail with the wrong end of the hammer,
and then blaming the hammer. You probably won't get a lot of help from the
professional carpenters if you do that.

 

[Cipher via AccessMonster.com]

Below you wrote:
"No. You *cannot* export data from a Form into a text file"...this is untrue
John, you can export to a Text File. The problem is that Access forces the
user to save it in a rediculus Formatted Text File filled with DASHES as it
is not possible to UNCHECK the Format choice..

If I import data into a Table and then use a FORM to access this information,
it is possible for me to ADD, DELETE and CHANGE data with the Form. I can
export data from the Form into a Text File,

No. You *cannot* export data from a Form into a text file, since forms do not
contain any data. You can export data FROM A TABLE (bound to a form or not, it
makes no difference) into a text file. The export wizard on the form is in
fact exporting data from a Table, not from the form.

but ACCESS refuses to write the
data back into the original text file. It will, however, write another text
file by appending the word 'Form' to the original name of the file and then
builds stupid '---' around the data. So, it IS possible for the Form to wride
the data back to a test file, unfortunatly, Microsoft felt that adding stupid
DASHES around the data would (somehow) make things much better.

Yep, the wizards do some pretty silly things.

So it is not me using the wrong tools it is Microsoft having no ability
whatsoever in the design of a Database Application. If Microsoft enabled me

[quoted text clipped - 3 lines]

Now I have to learn VBA because Microsoft is too laim...thanks for the
information.

VBA *IS* the means by which Access enables the design of a database
application. That's what its name means - Visual Basic for Applications. I'm
sorry you feel that the end-user wizards should have been designed to fit your
specific requirements and that no learning on your part should be required!

 

[Cipher via AccessMonster.com]

Well Dirk (I love your movies by the way):

Just think of the hammer working this way...
1) I have a text file comma delimited and each row is a record...ie a 2D text
array
2) Access is very happy reading this text file into a table.
3) Access is also very happy to represent the table into a Form...so far so
good?
4) If I use the Form to add and change data (which is easier than using the
Table or the Text file in Notepad (this is why we use Access and not Notepad),
why cant the Form change the Table and the TAble change the Text File. The
WHOLE text file is loaded into Access fo it is not impossible for Access to
Bacwards the data back to the original Text File. We are not all going to be
hooked into a giant network of users. Some of us are just one person and one
computer so there is no conflict with data purity.

Now I hear people on this site say over-and-over-again..."you cannot export a
text file from a Form", which is totally untrue, you can. Unfortunatly,
Access (what a stupid name for this nonaccess application) reformats the file
addind DASHES in between each record. If I thought that these DASHES were a
good idea, I would have placed them in my original text file. If I could
UNCHECK the box that reformates this export there would be no problem.
Unfortunatly Microsoft refuses ACCESS to the controls.

So, I have a table that I can change and ADD/DELETE records and export back
into the original text file, but I cannot change the table with a Form and
update the text file automatically...do I have this hammer definition
straight?

So I will have to stop what I was doing and then go away and learn VBA (I
thank the other participant telling me what I already knew what VBA stands
for) and then get back to my project.

As this application I am working on is a Single User with a Single machine
and not on a network, what other Database application could be used that will
load Text Files and add/delete/change data in a text file by reading the
whole file into the application, allowing me to change things and then write
the data back into the original text file?

ACCESS does not give me ACCESS!

I have a text file that is read into a Table. The file is not linked as I
wish to alter/add records and record data. I have generated a FORM that

[quoted text clipped - 15 lines]

the
original text file WITHOUT changing the format in any way...thanks all.

There is *no* program that can edit a text file in place, unless the
modified data to be stored is exactly the same size as the original text.
It is possible to directly overwrite an entire file with a new or modified
file, and a text-editing program, which Microsoft Access is not, may do
that, or it may make an effort to protect against the consequences of
possible failure by actually saving a new copy of the file, then renaming
the old copy, then renaming the new copy to the old name, and then deleting
the old copy.

Access is not a text editor. As a database program, it is designed to edit
structured data in tables. In addition, it provides mechanisms to import
and export data in various formats including text, so long as that data is
tabular in structure. Access is perfectly capable of importing a (tabular)
text file, editing it as a table, and then exporting it to a text file
again. YOu can even tell it to use the same name as the original text file
and overwrite that file.

However, these caveats apply:

1. If multiple people are going to be editing the imported data and
re-exporting it to the original text file, they have to be doing their edits
in the same Access database. You can't have person A import the data into
one database, and person B import it into another database, have them edit
the imported data, and then export it back to the same text file. If you
do, one user's version of the data will overwrite the other's version.

2. When exporting the data to a text file again, it may require some
tinkering to get the data into the exact same text format as it was
originally input. That is because Access is data-oriented, not
format-oriented.

Your message makes it clear that you do not know how to export your data
properly, and you are using the incorrect method to go about it. It's as
though you were trying to drive in a nail with the wrong end of the hammer,
and then blaming the hammer. You probably won't get a lot of help from the
professional carpenters if you do that.

 

[Dirk Goldgar]

Well Dirk (I love your movies by the way):

I'm not sure which Dirk you're referring to. I do suspect that my mother
named me for Dirk Bogarde, but she never admitted it.

Just think of the hammer working this way...
1) I have a text file comma delimited and each row is a record...ie a 2D
text
array
2) Access is very happy reading this text file into a table.
3) Access is also very happy to represent the table into a Form...so far
so
good?
4) If I use the Form to add and change data (which is easier than using
the
Table or the Text file in Notepad (this is why we use Access and not
Notepad),
why cant the Form change the Table and the TAble change the Text File.

Unless you've done something very wrong, the form *is* changing the table.
The form cannot directly change the text file, and the table can't do it
either -- not directly -- because the text file format is unstructured by
definition. Yes, you may happen to have a tabular format in the way the
text in this particular file is laid out, but there is nothing encoded in a
text file that makes it possible to work directly with records and fields.

The
WHOLE text file is loaded into Access fo it is not impossible for Access
to
Bacwards the data back to the original Text File. We are not all going to
be
hooked into a giant network of users. Some of us are just one person and
one
computer so there is no conflict with data purity.

If it's just you and you alone working with this file and database, some of
the potential problems that I and others have mentioned can be ignored.
That has not been clear up to now, at least not to me.

Now I hear people on this site say over-and-over-again..."you cannot
export a
text file from a Form", which is totally untrue, you can.

There's some sort of misunderstanding involved here. Access does let you
export a form to text, but what you get is neither the true appearance of
the form nor the raw text content. Instead, it gives you a text-formatted
representation of the form, laid out to be readable and, supposedly,
prettier than the raw text would be. It's of no use for what you want to
do. So we're all in agreement about that.

So, I have a table that I can change and ADD/DELETE records and export
back
into the original text file, but I cannot change the table with a Form and
update the text file automatically...do I have this hammer definition
straight?

So I will have to stop what I was doing and then go away and learn VBA (I
thank the other participant telling me what I already knew what VBA stands
for) and then get back to my project.

No, you don't. You have been using the wrong method to attempt to exportt
your data, but there is a better one. As we have all been telling you, you
should not be attempting to export the form. All you have to do is export
the *table* instead. Then, once you have chosen to export to text format,
the Text Export Wizard will start, and allow you to specify all kinds of
things about the output format, including that you want to export to
comma-delimited format.

You do not need a lick of VBA to use this method. The only hitch, as I
mentioned before, is that you may find that Access doesn't format some
fields exactly as you want. If, upon inspection of the output, you find
that to be the case, then you may get around the problem by outputting a
query of the table instead. In the query, you can format the fields
differently. But first, just see if exporting the table, rather than the
form, gets you the output you want.

ACCESS does not give me ACCESS!

You've been knocking on the wrong door.

 

[John W. Vinson]

Below you wrote:
"No. You *cannot* export data from a Form into a text file"...this is untrue
John, you can export to a Text File. The problem is that Access forces the
user to save it in a rediculus Formatted Text File filled with DASHES as it
is not possible to UNCHECK the Format choice..

Sorry, I misspoke. The wizard *pretends* to be exporting from the form, but
it's actually - behind the scenes - exporting from a Table. Sorry for the
confusion.

 

[Cipher via AccessMonster.com]

Dirk:

If one knew which door to knock on life would be simple. However, not knowing
which door or (for that matter) how many doors there actuall are, I can only
guess unless you (or somebody else here) can give me the information I need
to get this project finished. All I can do is tell you the problem and
hopefully you can open the door I am knocking on even though it is the wrong
door.

I have to ignore the Forms that edit the Tables, the customer can use the
Tables. What I now need to do is work on additional Forms that compute data
from various tables. To do this I will use ADO in VBA. I have run into a
problem, possibly you can help without giving me the Wrong Door again.

Here is the problem:
The following text gives a "User Undifined error" when it reaches:
Dim rs As ADODB.Recordset
Set rs=NEW ADODB.Recordset

Can you please tell me what is wrong with it. It is straight out of the
Programmers Reference Bible for Access.
Code Follows:

Dim rs As ADODB.Recordset
Set rs=NEW ADODB.Recordset
rs.ActiveConnection=CurrentProject.Connection
rs.Source="Inject_Cycle"
rs.CursorType=adOpenForwardOnly
rs.LockType=adLockReadOnly

endCode

Thank you Dirk
PS I think your mother is not telling you the truth

Well Dirk (I love your movies by the way):

I'm not sure which Dirk you're referring to. I do suspect that my mother
named me for Dirk Bogarde, but she never admitted it.

Just think of the hammer working this way...
1) I have a text file comma delimited and each row is a record...ie a 2D

[quoted text clipped - 9 lines]

Notepad),
why cant the Form change the Table and the TAble change the Text File.

Unless you've done something very wrong, the form *is* changing the table.
The form cannot directly change the text file, and the table can't do it
either -- not directly -- because the text file format is unstructured by
definition. Yes, you may happen to have a tabular format in the way the
text in this particular file is laid out, but there is nothing encoded in a
text file that makes it possible to work directly with records and fields.

The
WHOLE text file is loaded into Access fo it is not impossible for Access

[quoted text clipped - 4 lines]

one
computer so there is no conflict with data purity.

If it's just you and you alone working with this file and database, some of
the potential problems that I and others have mentioned can be ignored.
That has not been clear up to now, at least not to me.

Now I hear people on this site say over-and-over-again..."you cannot
export a
text file from a Form", which is totally untrue, you can.

There's some sort of misunderstanding involved here. Access does let you
export a form to text, but what you get is neither the true appearance of
the form nor the raw text content. Instead, it gives you a text-formatted
representation of the form, laid out to be readable and, supposedly,
prettier than the raw text would be. It's of no use for what you want to
do. So we're all in agreement about that.

So, I have a table that I can change and ADD/DELETE records and export
back

[quoted text clipped - 5 lines]

thank the other participant telling me what I already knew what VBA stands
for) and then get back to my project.

No, you don't. You have been using the wrong method to attempt to exportt
your data, but there is a better one. As we have all been telling you, you
should not be attempting to export the form. All you have to do is export
the *table* instead. Then, once you have chosen to export to text format,
the Text Export Wizard will start, and allow you to specify all kinds of
things about the output format, including that you want to export to
comma-delimited format.

You do not need a lick of VBA to use this method. The only hitch, as I
mentioned before, is that you may find that Access doesn't format some
fields exactly as you want. If, upon inspection of the output, you find
that to be the case, then you may get around the problem by outputting a
query of the table instead. In the query, you can format the fields
differently. But first, just see if exporting the table, rather than the
form, gets you the output you want.

ACCESS does not give me ACCESS!

You've been knocking on the wrong door.

 

[Cipher via AccessMonster.com]

I am using Access 2007 for this project

Well Dirk (I love your movies by the way):

I'm not sure which Dirk you're referring to. I do suspect that my mother
named me for Dirk Bogarde, but she never admitted it.

Just think of the hammer working this way...
1) I have a text file comma delimited and each row is a record...ie a 2D

[quoted text clipped - 9 lines]

Notepad),
why cant the Form change the Table and the TAble change the Text File.

Unless you've done something very wrong, the form *is* changing the table.
The form cannot directly change the text file, and the table can't do it
either -- not directly -- because the text file format is unstructured by
definition. Yes, you may happen to have a tabular format in the way the
text in this particular file is laid out, but there is nothing encoded in a
text file that makes it possible to work directly with records and fields.

The
WHOLE text file is loaded into Access fo it is not impossible for Access

[quoted text clipped - 4 lines]

one
computer so there is no conflict with data purity.

If it's just you and you alone working with this file and database, some of
the potential problems that I and others have mentioned can be ignored.
That has not been clear up to now, at least not to me.

Now I hear people on this site say over-and-over-again..."you cannot
export a
text file from a Form", which is totally untrue, you can.

There's some sort of misunderstanding involved here. Access does let you
export a form to text, but what you get is neither the true appearance of
the form nor the raw text content. Instead, it gives you a text-formatted
representation of the form, laid out to be readable and, supposedly,
prettier than the raw text would be. It's of no use for what you want to
do. So we're all in agreement about that.

So, I have a table that I can change and ADD/DELETE records and export
back

[quoted text clipped - 5 lines]

thank the other participant telling me what I already knew what VBA stands
for) and then get back to my project.

No, you don't. You have been using the wrong method to attempt to exportt
your data, but there is a better one. As we have all been telling you, you
should not be attempting to export the form. All you have to do is export
the *table* instead. Then, once you have chosen to export to text format,
the Text Export Wizard will start, and allow you to specify all kinds of
things about the output format, including that you want to export to
comma-delimited format.

You do not need a lick of VBA to use this method. The only hitch, as I
mentioned before, is that you may find that Access doesn't format some
fields exactly as you want. If, upon inspection of the output, you find
that to be the case, then you may get around the problem by outputting a
query of the table instead. In the query, you can format the fields
differently. But first, just see if exporting the table, rather than the
form, gets you the output you want.

ACCESS does not give me ACCESS!

You've been knocking on the wrong door.

 

[Dirk Goldgar]

Dirk:

If one knew which door to knock on life would be simple. However, not
knowing
which door or (for that matter) how many doors there actuall are, I can
only
guess unless you (or somebody else here) can give me the information I
need
to get this project finished. All I can do is tell you the problem and
hopefully you can open the door I am knocking on even though it is the
wrong
door.

That's not a problem. It's just not a good idea to begin the discussion
talking about what a lousy door this is, and how irresponsible the builder
was to have constructed a house with more than one door.

What I now need to do is work on additional Forms that compute data
from various tables. To do this I will use ADO in VBA. I have run into a
problem, possibly you can help without giving me the Wrong Door again.

Here is the problem:
The following text gives a "User Undifined error" when it reaches:

Interesting error message; are you really undefined? My guess is that the
message was really "User-defined type not defined".

Dim rs As ADODB.Recordset
Set rs=NEW ADODB.Recordset

Can you please tell me what is wrong with it. It is straight out of the
Programmers Reference Bible for Access.

At a guess, you don't have a reference set to the ActiveX Data Objects (ADO)
library. A reference to that library was included by default in Access
versions 2000 through 2003, but not in Access 2007.

There are two ways to approach this. The easier way, which I would
recommend against, is just to add that reference. You do that in the VB
Editor environment by clicking Tools -> References..., and then scrolling
down the list of available references to find "Microsoft ActiveX Data
Objects 2.x Library", and putting a check-mark in the box next to it. Then
your code will (probably) work.

However, ADO is really not the best choice to work with Access data. The
"Data Access Objects" (DAO) object library -- or in Access 2007, the ADE
object library -- is specially designed for work with Access, and provides
objects and properties that let you work fairly closely with the Jet
database engine. There was a time when Microsoft thought that ADO was the
direction of the future, but they have since changed their tune.

Access 2007 comes with a default reference set to the "Access dabase engine
object library", which is the enhanced, A2007 version of the DAO library.
It still goes by the tag "DAO", so I'll continue to refer it that way. For
work with Access (Jet) databases, this is generally the library to use.

However, the code to use DAO is somewhat different from the equivalent ADO
code, so your code copied from that book would have to be changed. If you
just want to get that code to work, and aren't interested in taking the time
now to learn how to use DAO instead of ADO, then just add the reference to
ADO as I described above. Otherwise, get back to me.

 

[Cipher via AccessMonster.com]

Hi Dirk:

All of the books I have reference ADO. Are there any references for DAO or
ADE you could point to for me (links) so I can obtain the correct code...
thanks.

Dirk:

[quoted text clipped - 8 lines]

wrong
door.

That's not a problem. It's just not a good idea to begin the discussion
talking about what a lousy door this is, and how irresponsible the builder
was to have constructed a house with more than one door.

What I now need to do is work on additional Forms that compute data
from various tables. To do this I will use ADO in VBA. I have run into a
problem, possibly you can help without giving me the Wrong Door again.

Here is the problem:
The following text gives a "User Undifined error" when it reaches:

Interesting error message; are you really undefined? My guess is that the
message was really "User-defined type not defined".

Dim rs As ADODB.Recordset
Set rs=NEW ADODB.Recordset

Can you please tell me what is wrong with it. It is straight out of the
Programmers Reference Bible for Access.

At a guess, you don't have a reference set to the ActiveX Data Objects (ADO)
library. A reference to that library was included by default in Access
versions 2000 through 2003, but not in Access 2007.

There are two ways to approach this. The easier way, which I would
recommend against, is just to add that reference. You do that in the VB
Editor environment by clicking Tools -> References..., and then scrolling
down the list of available references to find "Microsoft ActiveX Data
Objects 2.x Library", and putting a check-mark in the box next to it. Then
your code will (probably) work.

However, ADO is really not the best choice to work with Access data. The
"Data Access Objects" (DAO) object library -- or in Access 2007, the ADE
object library -- is specially designed for work with Access, and provides
objects and properties that let you work fairly closely with the Jet
database engine. There was a time when Microsoft thought that ADO was the
direction of the future, but they have since changed their tune.

Access 2007 comes with a default reference set to the "Access dabase engine
object library", which is the enhanced, A2007 version of the DAO library.
It still goes by the tag "DAO", so I'll continue to refer it that way. For
work with Access (Jet) databases, this is generally the library to use.

However, the code to use DAO is somewhat different from the equivalent ADO
code, so your code copied from that book would have to be changed. If you
just want to get that code to work, and aren't interested in taking the time
now to learn how to use DAO instead of ADO, then just add the reference to
ADO as I described above. Otherwise, get back to me.

 

[Dirk Goldgar]

Hi Dirk:

All of the books I have reference ADO. Are there any references for DAO or
ADE you could point to for me (links) so I can obtain the correct code...
thanks.

I don't have a link to a really good tutorial, but check out these pages on
Allen Browne's site:

http://www.allenbrowne.com/ser-04.html
DAO Object Model

http://www.allenbrowne.com/func-DAO.html
DAO Programming Code Examples

I notice, though, that Allen doesn't actually have good examples of the most
basic, "open recordset, update record, close recordset" type of operations.

Crystal Long has an excellent Access tutorial here:

http://www.accessmvp.com/strive4peace/

.... but it doesn't go into any details about DAO.

You may find some other good DAO tutorials if you search the web. There's
also a lot of good "how to" information at The Access Web:

http://www.mvps.org/access/

Unfortunately, I learned DAO mostly from the help files, from examples
posted in various locations (including these newsgroups), and by trial and
error. So I can't really recommend any online tutorials from personal
experience. I could recommend a few books, if you like.

 

[brlubman]

Hi Dirk:

All of the books I have reference ADO. Are there any references for DAO or
ADE you could point to for me (links) so I can obtain the correct code...
thanks.

Dirk:

[quoted text clipped - 8 lines]

wrong
door.

That's not a problem. It's just not a good idea to begin the discussion
talking about what a lousy door this is, and how irresponsible the builder
was to have constructed a house with more than one door.

What I now need to do is work on additional Forms that compute data
from various tables. To do this I will use ADO in VBA. I have run into a
problem, possibly you can help without giving me the Wrong Door again.

Here is the problem:
The following text gives a "User Undifined error" when it reaches:

Interesting error message; are you really undefined? My guess is that the
message was really "User-defined type not defined".

Dim rs As ADODB.Recordset
Set rs=NEW ADODB.Recordset

Can you please tell me what is wrong with it. It is straight out of the
Programmers Reference Bible for Access.

At a guess, you don't have a reference set to the ActiveX Data Objects (ADO)
library. A reference to that library was included by default in Access
versions 2000 through 2003, but not in Access 2007.

There are two ways to approach this. The easier way, which I would
recommend against, is just to add that reference. You do that in the VB
Editor environment by clicking Tools -> References..., and then scrolling
down the list of available references to find "Microsoft ActiveX Data
Objects 2.x Library", and putting a check-mark in the box next to it. Then
your code will (probably) work.

However, ADO is really not the best choice to work with Access data. The
"Data Access Objects" (DAO) object library -- or in Access 2007, the ADE
object library -- is specially designed for work with Access, and provides
objects and properties that let you work fairly closely with the Jet
database engine. There was a time when Microsoft thought that ADO was the
direction of the future, but they have since changed their tune.

Access 2007 comes with a default reference set to the "Access dabase engine
object library", which is the enhanced, A2007 version of the DAO library.
It still goes by the tag "DAO", so I'll continue to refer it that way. For
work with Access (Jet) databases, this is generally the library to use.

However, the code to use DAO is somewhat different from the equivalent ADO
code, so your code copied from that book would have to be changed. If you
just want to get that code to work, and aren't interested in taking the time
now to learn how to use DAO instead of ADO, then just add the reference to
ADO as I described above. Otherwise, get back to me.