Darren DICK
d.dick at uws.edu.au
Wed Apr 7 08:02:20 CDT 2004
Hi Andy et al
I figured it out phew...
Here is the code if anyone is interested
It allows for a single path to be stored then you can and import or link to
My.txtControlThatHoldsThePath may have the following as its value
EG C:\My Documents\My Folder\Inane Name\XYZ.dbf
First Create a new module and paste this into it
'determine the last backslash in a path then return file name from that path
'Code by Seth Galitzer
Public Function basParseFileName(strPath As String) As String
Dim intI As Integer ' Current starting position
Dim intL As Integer ' Last starting position
intI = InStr(strPath, "\") ' Initialize the starting position
While intI > 0 ' Repeat until no more "\" chars
intL = intI ' Save the last starting position
intI = InStr(intI + 1, strPath, "\") ' Get the next starting position
Wend
basParseFileName = Mid(strPath, intL + 1) ' Chop off the filename
End Function
The in the button you want to perform the import or link with
Private Sub SomeButton_Click()
Dim intX As Integer
intX = Len(basParseFileName(Me.txtControlThatHoldsThePath))
DoCmd.TransferDatabase acImport, "dBase IV", Mid(Me.txtControlThatHoldsThePath, 1, Len(Me.txtControlThatHoldsThePath) - intX - 1),
acTable, basParseFileName(Me.txtControlThatHoldsThePath), "tblStudents", False
'The trick is in the "-1" just after the intX parameter in the Transfer Database syntax
'The Transfer database monster wants to see the full folder path in the 3rd parameter minus the actual filename
'and wants to see the file name only in the 5th parameter. So far no problem
'BUT!!! - the trick is Access doesn't wanna see the last backslash either before the file name: Go figure???
'So assume you have a folder and file name like C:\My Documents\My Folder\Inane Name\XYZ.dbf
'Access is working with C:\My Documents\My Folder\Inane NameXYZ.dbf. 'Note the missing last backslash
'Access obviously compensates for it somewhere internally
end sub
Hope this helps someone
----- Original Message -----
From: "Andy Lacey" <andy at minstersystems.co.uk>
To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com>
Sent: Wednesday, April 07, 2004 5:13 PM
Subject: RE: [AccessD] A2K: Another Trasnfer Database Q
> Darren
> What's the code? Have you tried specifying the folder in the 3rd parameter,
> then just the dbf name in the 5th?
>
> -- Andy Lacey
> http://www.minstersystems.co.uk
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
> > Sent: 07 April 2004 03:59
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] A2K: Another Trasnfer Database Q
> >
> >
> > Hi Charlotte
> > Yes I am deleting the link before I start using
> > Docmd.DeleteObject blah blah
> >
> > Re the file being moved any where, I can move the file
> > anywhere and 'redo' the
> > link using the new path, so long as the path is in the root
> > directory (C:\) IE I can change the name of the file from
> > SIDS.dbf to S_ids.dbf or whatever, but it must
> > stay in the root directory. If I move it to "any" directory
> > other than the root directory (C:\) it fails eg
> > C:\ABC\SIDS.dbf <sigh> I dunno
> >
> >
> > ----- Original Message -----
> > From: "Charlotte Foust" <cfoust at infostatsystems.com>
> > To: "Access Developers discussion and problem solving"
> > <accessd at databaseadvisors.com>
> > Sent: Wednesday, April 07, 2004 10:25 AM
> > Subject: RE: [AccessD] A2K: Another Trasnfer Database Q
> >
> >
> > > Are you deleting the link before you try to relink? I
> > would expect it
> > > to fail if you moved the file *anywhere*. That's just the
> > way Access
> > > links work. Are you using the entire path and filename in the
> > > filename argument?
> > >
> > > Charlotte Foust
> > >
> > > -----Original Message-----
> > > From: Darren DICK [mailto:d.dick at uws.edu.au]
> > > Sent: Tuesday, April 06, 2004 3:51 PM
> > > To: AccessD List
> > > Subject: [AccessD] A2K: Another Trasnfer Database Q
> > >
> > >
> > > Hello all
> > > When I try to import or link to a DBF or XLS file in the root
> > > directory all is well. When I move the dbf or XLS file to ANY
> > > dorectory/folder the import or link fails. Access spits up a long
> > > error message with Error Number Saying that the path is
> > invalid. That
> > > I should go and check that it exists etc. Of course it
> > exists I just
> > > created it for testing as well as using pre-existing
> > directories. The
> > > Docmd.Transfer works - why would it fail when I use a folde? Yes I
> > > have tried quotes around the path and xls specs
> > >
> > > Any taker?
> > >
> > > Many thanks in advance
> > >
> > > Darren
> > >
> > > --
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > > --
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/a> ccessd
> > Website:
> > http://www.databaseadvisors.com
> >
> >
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com