Help with VBA "DoCmd.TransferSpreadsheet acImport ......."   

2003

I would like the data imported into a table in a non-current() database.

DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & strTableName & ".xls", True
(The above command works but .... I want the data to go to strTableName in another database]

Any coding help appreciated

TIA EagleOne

date: Wed, 30 Jul 2008 17:13:05 -0400   author:   unknown

Re: Help with VBA "DoCmd.TransferSpreadsheet acImport ......."   

You need to instantiate another instance of Access, set its CurrentDatabase 
appropriately, and then do your import. Something like the following 
untested air-code:

Dim appAccess As Access.Application

  Set appAccess = New Access.Application
  appAccess.OpenCurrentDatabase "C:\Folder\File.mdb"
  appAccess.DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & 
strTableName & ".xls", True
  appAccess.CloseCurrentDatabase
  Set appAccess = Nothing

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


 wrote in message 
news:f1m194pm2qnt2qruo3gter77g14jvmq8id@4ax.com...
> 2003
>
> I would like the data imported into a table in a non-current() database.
>
> DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & strTableName 
> & ".xls", True
> (The above command works but .... I want the data to go to strTableName in 
> another database]
>
> Any coding help appreciated
>
> TIA EagleOne

date: Wed, 30 Jul 2008 20:31:29 -0400   author:   Douglas J. Steele

Re: Help with VBA "DoCmd.TransferSpreadsheet acImport ......."   

Excellent and Complete answer.  


"Douglas J. Steele" wrote:

> You need to instantiate another instance of Access, set its CurrentDatabase 
> appropriately, and then do your import. Something like the following 
> untested air-code:
> 
> Dim appAccess As Access.Application
> 
>   Set appAccess = New Access.Application
>   appAccess.OpenCurrentDatabase "C:\Folder\File.mdb"
>   appAccess.DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & 
> strTableName & ".xls", True
>   appAccess.CloseCurrentDatabase
>   Set appAccess = Nothing
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
>  wrote in message 
> news:f1m194pm2qnt2qruo3gter77g14jvmq8id@4ax.com...
> > 2003
> >
> > I would like the data imported into a table in a non-current() database.
> >
> > DoCmd.TransferSpreadsheet acImport, , strTableName, myPath & strTableName 
> > & ".xls", True
> > (The above command works but .... I want the data to go to strTableName in 
> > another database]
> >
> > Any coding help appreciated
> >
> > TIA EagleOne 
> 
> 
>

date: Thu, 31 Jul 2008 04:04:03 -0700   author:   EagleOne@microsoftdiscussiongroups