Delete multiple mdb database fields & export 1 remaining field as plain text

  • Thread starter Thread starter jbl
  • Start date Start date
J

jbl

I have several mdb databases that each contain only one table named
Clips.
There are 26 fields, as follows:

Clip Key
ClipsUploaded
City
Station
CallLetter/Network
Program
Time
Broadcast Date
Recorded Date
Name
Counter
TapeID
Description
clpSummary
TypeLetter
Type
CategoryLetters
Category
Bcategory
Ditch
TimeIn
TimeOut
Duration
MonitorID
Ptype
clOpening

I need a macro, or the simple and quickest way to delete all fields
except Description.

(actually to just export contents the 1 field, Description as text if
possible)

Then export as a .txt file with no quotation marks.

If I had something that worked I would need to do this on 18- 20
different database files each day.

Any help or suggestions would be appreciated.

I have used Access for a while but never have attempted to create a
macro.

thanks

jbl
 
rather than altering the table, you can create a query to pull just that one
field from the table, and output the query's dataset to a text file.

hth
 
rather than altering the table, you can create a query to pull just that one
field from the table, and output the query's dataset to a text file.

hth
Click to expand...

Well I did get a query to work but;

I work with 18-20 individual mdb databases. Each is generated new each
day by a 3rd party. Each contains only one table. No queries, forms,
macros, etc...

Is there a better way than to

Create a permanent dummy mdb file with the query.
Open each new mdb file individually and import the query from the
dummy file, then export query results as text.

jbl
 
You can create a query in DatabaseA that connects to another database
without requiring that you create a linked table. The SQL looks something
like:

SELECT GroupId, GroupDs FROM [;Database=D:\Folder\File.mdb].Groups

(where Groups is the name of the table in the external database (and GroupId
and GroupDs are fields in that table).

As long as you know the full path to the database, you can easily modify
your query:

Dim qdfCurr As DAO.QueryDef
Dim strPath As String
Dim strSQL As String

strPath = "D:\Folder\File1.mdb"

' Check that the file actually exists
If Len(Dir(strPath)) > 0 Then
strSQL = "SELECT Field1, Field2 FROM [;Database=" & _
strPath & "].Table"
Set qdfCurr = CurrentDb.QueryDefs("ExistingQuery")
qdfCurr.SQL = strSQL
End If

The query named ExistingQuery now points to the other database.
 
i never knew you could do that, Doug - cool! will that work in more complex,
linked multi-table queries, too? and is there a substantial time difference
(better or worse) than creating a link via code, in order to run a query?
tia, tina


Douglas J. Steele said:
You can create a query in DatabaseA that connects to another database
without requiring that you create a linked table. The SQL looks something
like:

SELECT GroupId, GroupDs FROM [;Database=D:\Folder\File.mdb].Groups

(where Groups is the name of the table in the external database (and GroupId
and GroupDs are fields in that table).

As long as you know the full path to the database, you can easily modify
your query:

Dim qdfCurr As DAO.QueryDef
Dim strPath As String
Dim strSQL As String

strPath = "D:\Folder\File1.mdb"

' Check that the file actually exists
If Len(Dir(strPath)) > 0 Then
strSQL = "SELECT Field1, Field2 FROM [;Database=" & _
strPath & "].Table"
Set qdfCurr = CurrentDb.QueryDefs("ExistingQuery")
qdfCurr.SQL = strSQL
End If

The query named ExistingQuery now points to the other database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jbl said:
Well I did get a query to work but;

I work with 18-20 individual mdb databases. Each is generated new each
day by a 3rd party. Each contains only one table. No queries, forms,
macros, etc...

Is there a better way than to

Create a permanent dummy mdb file with the query.
Open each new mdb file individually and import the query from the
dummy file, then export query results as text.

jbl
Click to expand...
Click to expand...
 
Yes, it works for multi-table queries (and the tables don't even have to be
in the same database).

I don't believe there's any difference in performance compared to using a
linked table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tina said:
i never knew you could do that, Doug - cool! will that work in more
complex,
linked multi-table queries, too? and is there a substantial time
difference
(better or worse) than creating a link via code, in order to run a query?
tia, tina


Douglas J. Steele said:
You can create a query in DatabaseA that connects to another database
without requiring that you create a linked table. The SQL looks something
like:

SELECT GroupId, GroupDs FROM [;Database=D:\Folder\File.mdb].Groups

(where Groups is the name of the table in the external database (and GroupId
and GroupDs are fields in that table).

As long as you know the full path to the database, you can easily modify
your query:

Dim qdfCurr As DAO.QueryDef
Dim strPath As String
Dim strSQL As String

strPath = "D:\Folder\File1.mdb"

' Check that the file actually exists
If Len(Dir(strPath)) > 0 Then
strSQL = "SELECT Field1, Field2 FROM [;Database=" & _
strPath & "].Table"
Set qdfCurr = CurrentDb.QueryDefs("ExistingQuery")
qdfCurr.SQL = strSQL
End If

The query named ExistingQuery now points to the other database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


jbl said:
rather than altering the table, you can create a query to pull just
that
one
field from the table, and output the query's dataset to a text file.

hth

Well I did get a query to work but;

I work with 18-20 individual mdb databases. Each is generated new each
day by a 3rd party. Each contains only one table. No queries, forms,
macros, etc...

Is there a better way than to

Create a permanent dummy mdb file with the query.
Open each new mdb file individually and import the query from the
dummy file, then export query results as text.

jbl
Click to expand...
Click to expand...
Click to expand...
 
You can create a query in DatabaseA that connects to another database
without requiring that you create a linked table. The SQL looks something
like:

SELECT GroupId, GroupDs FROM [;Database=D:\Folder\File.mdb].Groups

(where Groups is the name of the table in the external database (and GroupId
and GroupDs are fields in that table).

As long as you know the full path to the database, you can easily modify
your query:

Dim qdfCurr As DAO.QueryDef
Dim strPath As String
Dim strSQL As String

strPath = "D:\Folder\File1.mdb"

' Check that the file actually exists
If Len(Dir(strPath)) > 0 Then
strSQL = "SELECT Field1, Field2 FROM [;Database=" & _
strPath & "].Table"
Set qdfCurr = CurrentDb.QueryDefs("ExistingQuery")
qdfCurr.SQL = strSQL
End If

The query named ExistingQuery now points to the other database.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




Well I did get a query to work but;
Click to expand...
I work with 18-20 individual mdb databases. Each is generated new each
day by a 3rd party. Each contains only one table. No queries, forms,
macros, etc...
Click to expand...
Is there a better way than to
Click to expand...
Create a permanent dummy mdb file with the query.
Open each new mdb file individually and import the query from the
dummy file, then export query results as text.
Click to expand...
jbl- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Doug, I think I see how that would work.
My problem is that each file name is unique as the date & time of day
is appended to each file each day
ie... Remote100_08-06-07 1824.mdb, Remote100_08-12-07 1654.mdb
could something like

Remote*.mdb

be used for the file names?

jbl
 
Back
Top