• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

BrewDog .. Import Access File

BrewDog.. you once gave me the following code to import an excel spreadsheet automatically into the database.  I'm looking to modify the  ** section ** (with asterics) so that Access 97 file is imported rather than an excel file.
//////////////
dim varFile as variant
varFile = dir("c:\TEST\FILE1.XLS")

if len(varFile) > 1 then

''' here is where I need to edit
           ***do.cmd transferspreadsheet, etc.***
   docmd.transferspreadsheet acimport,

acspreadsheettypeExcel97, "File1 Table", varFile
   currentdb.execute("insert into [Quote Table] select * from [File1 Table]")
   KILL varFile
   docmd.deleteobject actable, "File1 Table"
else
   msgbox "The file 'File1.xls' is not in the specified directory."
end if


Can you help?
0
KathyBrowning
Asked:
KathyBrowning
  • 4
  • 3
  • 2
  • +1
1 Solution
 
JimMorganCommented:
Kathy:

What you might want to do is just link into the Access 97 table inside the foreign database and extract the data that you need.

I'm not sure if there is an automation command to import another table from another DB.  If you use  DoCmd.RunCommand accmdImport  , you get an open dialog box to select the db and table you want to import.  The same thing happens if you use  DoCmd.RunCommand accmdLinkTables.

If you know the DB name and the table name, you can link the table directly.

    Dim tdf as TableDef
    Dim strTableName as String
    Dim strDB as String
    strTableName = "TableName"
    strDB = "DBName"
    currentdb().TableDefs.Delete strTableName      '  if it currently exists in this db
    Set tdf = dbsDb.CreateTableDef(strTableName )   ' to create new table
    tdf.SourceTableName = strTableName
    tdf.Connect = ";DATABASE=" & strDB
    currentdb().TableDefs.Append tdf   ' to add the data

Jim
0
 
KathyBrowningAuthor Commented:
The problem with what you suggested is that the data is imported from an empty shell db(with one record).  The data is then imported into the the master database.  So where you delete the table if it exist would present problems.

The reason we have a duplicated database is because we have a VB5.0 executable that allows independent contracters to enter over 100 fields of information onto a floppy disk.  (Thus not necessarily having Access on their PC).  When the floppy disk is returned, the data can be automatically importated with code.

Why not go to the net?  Because the contractors (a lot of the time.. do not have internet access).

Kathy
0
 
brewdogCommented:
So this would be a single table in an Access database, right? Try this:

dim varFile as variant
varFile = dir("c:\TEST\FILE1.MDB")

if len(varFile) > 1 then
   currentdb.execute "Drop Table [File1 Table]"
   docmd.transferdatabase acImport, "Microsoft Access", varFile, "File1 Table", "File1 Table"
   currentdb.execute("insert into [Quote Table] select * from [File1 Table]")
   KILL varFile
else
   msgbox "The file 'File1.xls' is not in the specified directory."
end if

Hope that helps . . .

brewdog
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
KathyBrowningAuthor Commented:
Brewdog, I'm confused about the drop table statement.  What exactly does this do?  And in fact, there are multiple tables in the database that I will be doing this for.  All though it's essentially, one client updating about 100 fields, since they are related, it will affect multiple tables.

I've increased points to 100.  Thanks for your help.
0
 
brewdogCommented:
The Drop Table statement is the same thing as the

DoCmd.DeleteObject acTable, "Your Table"

statement we were using before, but (I've discovered) the DoCmd will give an error if the table isn't there, while the Drop Table won't. So I've changed how I get rid of tables. Sorry, I should have explained that.

The import part should still be fine no matter how many tables are in the database from which you're importing. However, if you are using that source file to add records to multiple tables in your main database, you'd need to replicate the "Insert Into" part, specifying the fields you need to grab data from and put them into. Something along these lines, maybe (replacing the

  currentdb.execute("insert into [Quote Table] select * from [File1 Table]")

line):

  currentdb.execute("insert into [Quote Table] (Field1, Field4, Field5, Field9) select Field1, Field4, Field5, Field9 from [File1 Table]")
  currentdb.execute("insert into [Customer Table] (Field2, Field3, Field7, Field14) select Field2, Field3, Field7, Field14 from [File1 Table]")
  currentdb.execute("insert into [Accounting Table] (Field6, Field8, Field10, Field13) select Field6, Field8, Field10, Field13 from [File1 Table]")

Does that make more sense?
0
 
KathyBrowningAuthor Commented:
Thanks BrewDog, everything works great.  Submit the answer.

Kathy
0
 
brewdogCommented:
Actually, you should be able to go to the upper right-hand corner of my comment and click "Accept Comment As Answer." Nice feature EE added. Saves time and money. :o)

Good to work with you again, Kathy.
0
 
lmerrellCommented:
brewdog - have you forgot about this one?  ;-)
0
 
brewdogCommented:
Yeah, guess I had. Thanks for the reminder -- looks like Kathy had forgotten about it, too. :o)
0
 
lmerrellCommented:
Just trying to keep you afloat, brewdog.  ;-)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now