drop table, import delimited text

[mark]

Hello.

I'm very familiar with VBA programming in Excel, but not in Access.

I need to:

1) Drop a given table, call it "tblData"
2) Import a delimited text file to recreate the table, call it "tblData.txt"
to table "tblData"


Can anyone help me with knowing how to do that in VBA in Access?

Alternatively, I could get the Access app to just connect to our Oracle
database, but I didn't find a path to do that in Access.

In Excel, it works from
Data-Import External Data - Import Data . From there, you choose 'New
Source' the first time, one of the option is Oracle, and it connects
nicely... even offers to put the data in your Pivot.

The object of this exercise is to run some data out of some Oracle tables,
into an Excel Pivot.

But, in some runs, the data will exceed Excel 2003's sheet row limit, and
we're not ready to have everybody use Excel 2007.

Thanks.

 

[mark]

okay, I'm part way there... I searched and copied off of someone else... have
the drop part going fine.

now on to the others.

 

[John Nurick]

I don't recall a 64-character limit on Excel paths in Access 97 (there
was for dBASE paths). You can work round by using the equivalent short
path, most easily obtainable with this little bit of code posted
recently by Pieter Wijnen:

Private Declare Function APIGetShortPath Lib "kernel32" _
Alias "GetShortPathNameA" _
(ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long) As Long

Public Function GetShortPath(ByVal LongName As String) As String
Dim ShortName As String * 256

APIGetShortPath LongName & VBA.vbNullChar, ShortName,
VBA.Len(ShortName)
GetShortPath = VBA.Left(ShortName, VBA.InStr(ShortName,
VBA.vbNullChar) - 1)
End Function


Normally it's bad practice to drop a table and create a replacement as
part of ordinary operations. Instead, use a delete query to empty the
table and then import the new data to the same table.

 

[mark]

okay, I'm using Access 2000, so I think you're telling me about a problem I
might have had, with the path info.

Normally it's bad practice to drop a table and create a replacement as
part of ordinary operations. Instead, use a delete query to empty the
table and then import the new data to the same table.

Okay, thanks for mentioning that. I guess it makes sense, but like I said,
although I have a bunch of experience automating Excel, Access is new to me.

I'm having some data import errors, but I'll get around them. Excel handles
them fine. It's just type conversion stuff.

I'll look into how to do a delte query, and then work on the code to import
the new data, soon.

All I've ever done with Access code was a line or two stuff here or there,
and usually called that from an Access instance I initiated in Excel. A time
or two, I've called someone else's code that they already wrote in Access.

But that's it.

Can you offer me an explanation as to why Access won't let you record VBA
code, yet knows all about how to convert a 'Macro' to a 'Module' ? (If you
just say, "Microsoft wrote it that way.", okay, but I'm wondering if there's
a background logical reason?

Obviously, recorded code is junk as far as end product applications, but if
you're trying to do something you never did before, it can often give you a
clue on how one might approach that task.

Thanks for the help.

 

[John Nurick]

Sorry - I got distracted and answered someone else's question<g>.

I've never worked with Oracle, but Access uses ODBC to work with an
Oracle database. You could set up a linked table in Access connected
to a view into the Oracle data, or write VBA code to use the
ADODB.Connection object. See
http://www.carlprothman.net/Default.aspx?tabid=81 for ODBC connection
string formats.

I don't know why Access has never had a 'macro' recorder. There may be
some deep technical reason going back to the days before the concept
of 'Microsoft Office' was invented, when Word, Excel, Access and
PowerPoint were developed independently.

Also, the correspondence between actions in the UI and VBA or SQL code
is much weaker when it comes to database operations. For example, if
the task is to update records according to some criterion, the UI
options are basically

(a) Open the table in datasheet view, filter it and use the
Edit|Replace command. This isn't a sane basis for updating records
under program control.

(b) Create the appropriate update query and execute it. If this
process was recorded, a duplicate query would be created every time
the 'macro' was run, which again doesn't make sense.

(c) Open a recordset on the table and write and execute the SQL
statement corresponding to the query in (b). But doing this means
executing VBA statements in the Immediate pane - so you're past the
stage of needing a recorder!

 

[mark]

thanks for the info.

I'm not sure yet whether I'll try the data connection suggestion for this
tool, or not. There are still open questions on it's scheduling, in the
definitions..

But thanks for the info, I'll probably give it a try at some point whether I
use it for this, or not!