Ken, Please Help, data going from Access to Excel

[Edward Letendre]

Hello to the experts here. I have a request for some help in coding a module
that will load data from a series of joined tables to get the query I need to
make a header for a seg p1 file. I have a basic set of code such as the
following:

Dim xlapp As Excel.Application
Dim xlbook As Excell.Workbook
Dim xlsheet As Excel.Worksheet
Dim hcount As Integer
Dim headquery As QueryDef

Set db = CurrentDb

Set headquery = db.CreateQueryDef()
headquery.Name = "header_3"

from the above code, I hope to open the header_3 query to begin loading my
header file. From there I need to load 4 key pieces of information, the
prospect name, client name, line name and contractor associated with this
line data.

The next concern is a series of queres that will load some other data to
form something called the projection. This project is made up of three
values that are retried with a series of select statements that again join
tables. I don't have these queries ready as of yet, but I have a source that
I can check to get an answer I hope.

Anway, where my problem comes in is how to I access the columns of a query
from the querydef that I created to link and open the query. Once I have
done that, how do I get the data into an excel spreadsheet in a specific
order and location? I have some excel examples from another website, but I
am lost as to what to do with them. I can post them for someone to look over
and give me a better explanation or idea as to what I should do to get things
working.

You guys never let me down before so please help if you can.

Edward Letendre.

 

[Ken Snell [MVP]]

Edward -

I'm puzzled by why you would create a querydef and then start "filling" it
with data? Is it your desire to create the contents of a data source/file by
writing data/records into this "querydef"?

A "querydef" is created for the purpose of creating a query -- consisting of
an SQL statement that will perform some type of "query action".

From your description, it sounds as if you want to create a text file that
has a specific structure and content? I don't know what a "seg p 1" file is,
so I'm guessing here.

You can read the field names (column names) of a query that is opened as a
recordset in this manner:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngLoop As Long
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("QueryName", dbOpenDynaset)
For lngLoop = 0 To rst.Fields.Count - 1
Debug.Print rst.Fields(lngLoop).Name
Next lngLoop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

As for the other questions, I just don't understand well enough what you're
wanting to do -- you seem to want to do a number of things? Let's start with
a single question and see what we can figure out.

 

[Edward Letendre]

yes, I see the light...I just read a chaper from an access 2000 book. As I
said before, I am new to this stuff and the querydef stuff is just a
structure so to speak to hold a select statement you build on the go. I see
the need for a record set. as you pointed out so well.

Also from the article I have read that once you have opened the recordset,
you can refer to individual columns from the table (or what I am hoping can
be a query, that is where I got lost at the beginning of this with querydef).
In this case, I have created a query, or view if you will, that joins some
tables to make the header.

For my second part here and now, once I have the recordset opened and such,
and I refer to say the first column of the recordset, can I do it this way
within access 97:

data1 as integer (assuming here for now that fields(0) is an integer)

rst.fields(0) = data1

withithis little bit of code, I hope to take field(0) or field 1 and place
it in a variable as a tempoary holding place. From this point, I hope to
eventually take field(0) or field 1 and send it to a spreadsheet (an excel
file) so that once I have all the fields of the two or three queries or views
as it were gathered, I need to place them in an excel file and then save this
excel file.

Evenutally I will need to convert the excel file to a csv (or comma
delimited file or such to be converted to a text file). The whole purpose of
this operation is to create part one of a two part file, called a seg p1
file. In my case, a seg p1 file is a seismic file that is associated with
seismic data (a line of data describing a point on a map by its latitue,
longitue, east, north and elevation values. From there, you have a name that
makes up a series of points and their named location by a field called a shot
point.) So a seg p1 file would look like the following:

H --to identify the header part of the file
LINE : 45
CLIENT : CALGARY OIL & GAS
prospect : CARL 3D
contractor : ODIE EXPLORATION
filename :
projection : U.T.M


[LINE] [POINT] [LAT] [LONG] [EAST] [NORTH] [ELE] [COMMENT
45 1201 557190 10124543
45 1202 568001 10112356

Also in my case, I have the data for the [LINE], [POINT], [LAT], [LONG],
[EAST], [NORTH] and [COMMENT] columns, but the header data is messed up and
needs to come out of the access database and then be re-created and
re-ordered.

That being, the line name, cilent name, prospect name, contractor name,
projection, etc. I have some select statements or views or queries that I
can create to get the data I need. From this point, I can then hopefully
process each "header" into an excel spreadsheet and then manipulate it any
way I can to get it to complete the seg p1 file in all of its glory. I know
it still sound counfusing, but I am working on it.

Something else to consider at this point, is that each record in the table
is identified by a uniqueid value. So each linked table that makes up a
single header would be identified by say value 1, 2, 3, etc. In the body or
data section, you have the following values:

unique id entry_point, shot_point, lat, long, etc.
1 1 1201
1 2 1202
1 3 1203, etc.

so the uniqueid (in this case 1) would be related to header 1 in the header
table, for each entry of data, you have an entry_point column (1,2,3,etc.
until all entries are there), along with a shot_point which changes for each
record and may not be unique within the table, as the same shot point value
can be reused in the line data table. The lat, long, east, north and
elevation values are also there for each entry in the line data table.

Thus I have a series of tables that were used to store this data and I need
to get the data out of the tables in a series of joins, views, queries, etc.
and then send the data to hopefully an excel file so that I can manipulate
the data and shape it the way I want and need to create the seg p1 files.

Edward Letendre.

 

[Ken Snell [MVP]]

Edward -

I'm tied up on a few things so I'll be a bit delayed in replying to your
note
--

Ken Snell
<MS ACCESS MVP>

yes, I see the light..

< snipped >

 

[Ken Snell [MVP]]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

For my second part here and now, once I have the recordset opened and
such,
and I refer to say the first column of the recordset, can I do it this way
within access 97:

data1 as integer (assuming here for now that fields(0) is an integer)

rst.fields(0) = data1

withithis little bit of code, I hope to take field(0) or field 1 and place
it in a variable as a tempoary holding place.

Yes, if rst is the recordset object, you can refer to value of the first
column in the recordset via
rst.Fields(0).Value

for example:
MyVariable = rst.Fields(0).Value

If you want to write data into a field in an opened recordset, the recordset
must be updatable -- meaning that the query is updatable, and that you
properly set the options for the recordset to tell ACCESS that the recordset
may be edited/appended to.

Then, assuming that you opened a DAO recordset, you must tell ACCESS to
begin editing the recordset, and then edit it, and then update it:
rst.Edit
rst.Fields(0).Value = MyVariable
rst.Update

From this point, I hope to
eventually take field(0) or field 1 and send it to a spreadsheet (an excel
file) so that once I have all the fields of the two or three queries or
views
as it were gathered, I need to place them in an excel file and then save
this
excel file.

If you can create a single query to provide all the data, you can export
that query (must be saved) via DoCmd.TransferSpreadsheet action.

Evenutally I will need to convert the excel file to a csv (or comma
delimited file or such to be converted to a text file).

ACCESS can create a csv file from a query directly without going through
EXCEL. See TransferText in Help file:
DoCmd.TransferText

Although I'm not sure if this method will be useful, seeing the structure of
the text file that you show below.

Otherwise, EXCEL can create a csv file via VBA, or you can save an EXCEL
file as a csv file using ACCESS VBA.

The whole purpose of
this operation is to create part one of a two part file, called a seg p1
file. In my case, a seg p1 file is a seismic file that is associated with
seismic data (a line of data describing a point on a map by its latitue,
longitue, east, north and elevation values. From there, you have a name
that
makes up a series of points and their named location by a field called a
shot
point.) So a seg p1 file would look like the following:

H --to identify the header part of the file
LINE : 45
CLIENT : CALGARY OIL & GAS
prospect : CARL 3D
contractor : ODIE EXPLORATION
filename :
projection : U.T.M


[LINE] [POINT] [LAT] [LONG] [EAST] [NORTH] [ELE] [COMMENT
45 1201 557190 10124543
45 1202 568001 10112356

Also in my case, I have the data for the [LINE], [POINT], [LAT], [LONG],
[EAST], [NORTH] and [COMMENT] columns, but the header data is messed up
and
needs to come out of the access database and then be re-created and
re-ordered.

That being, the line name, cilent name, prospect name, contractor name,
projection, etc. I have some select statements or views or queries that I
can create to get the data I need. From this point, I can then hopefully
process each "header" into an excel spreadsheet and then manipulate it any
way I can to get it to complete the seg p1 file in all of its glory. I
know
it still sound counfusing, but I am working on it.

Something else to consider at this point, is that each record in the table
is identified by a uniqueid value. So each linked table that makes up a
single header would be identified by say value 1, 2, 3, etc. In the body
or
data section, you have the following values:

unique id entry_point, shot_point, lat, long, etc.
1 1 1201
1 2 1202
1 3 1203, etc.

so the uniqueid (in this case 1) would be related to header 1 in the
header
table, for each entry of data, you have an entry_point column (1,2,3,etc.
until all entries are there), along with a shot_point which changes for
each
record and may not be unique within the table, as the same shot point
value
can be reused in the line data table. The lat, long, east, north and
elevation values are also there for each entry in the line data table.

Thus I have a series of tables that were used to store this data and I
need
to get the data out of the tables in a series of joins, views, queries,
etc.
and then send the data to hopefully an excel file so that I can manipulate
the data and shape it the way I want and need to create the seg p1 files.

Edward Letendre.

The rest of what you've outlined above is good info, but goes beyond what we
might do at this moment....