VBA, Linking Text File in DAO

Linking Text File in DAO - does not work.

Author	Message
Andr #1 / 5	Linking Text File in DAO - does not work. There is exist function: '********************************************** ' Return True if link was created seccsessfully '******************************************** Public Function AddLinkTableTXT(ByVal sLinkTName As String, ByVal sSourceFName As String, ByVal sSpecification As String) As Boolean ' sLinkTName - Name of table in curent DB ' sSourceTName - real table name (from NameMDB) ' NameMDB - filename of DB where the sSourceTName is. Dim tDef As TableDef Dim dbc As Database Set dbc = CurrentDb On Error Resume Next dbc.TableDefs.Delete (sLinkTName) dbc.TableDefs.Refresh On Error GoTo ErrLink Set tDef = New TableDef tDef.name = sLinkTName tDef.Connect = "Text;DSN=" + sSpecification + ";FMT=Fixed;HDR=NO;IMEX=2;Database=" + GetPath(sSourceFName) tDef.SourceTableName = GetName(sSourceFName) + "." + GetExt(sSourceFName) DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, sSourceFName, False dbc.TableDefs.Append tDef AddLinkTableTXT = True Exit Function ErrLink: AddLinkTableTXT = False Exit Function End Function ************************************************************************ ***************************** Each time I'm getting False as a result because of error : 3625 The text file specification 'Account_PP_Link_Specification' does not exist. You can't import, export, or link using the specification. BUT: Specification 'Account_PP_Link_Specification' does exist !!!!!!!!!!!!!!! (I've checked it in system table). And I can upload this ... file by command: 'DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, sSourceFName, False' which works just perfect! But I do not want to do this! After link any text file "by hand" everything is works! Text file links by this function without any problem. Any solutions? HELP !!!!!!!!!!!
Sat, 11 Oct 2003 01:58:00 GMT

Alex Dybenk #2 / 5	Linking Text File in DAO - does not work. Try to use tDef.SourceTableName = GetName(sSourceFName) + "#" + GetExt(sSourceFName) Alex Quote: > There is exist function: > '********************************************** > ' Return True if link was created seccsessfully > '******************************************** > Public Function AddLinkTableTXT(ByVal sLinkTName As String, ByVal > sSourceFName As String, ByVal sSpecification As String) As Boolean > ' sLinkTName - Name of table in curent DB > ' sSourceTName - real table name (from NameMDB) > ' NameMDB - filename of DB where the sSourceTName is. > Dim tDef As TableDef > Dim dbc As Database > Set dbc = CurrentDb > On Error Resume Next > dbc.TableDefs.Delete (sLinkTName) > dbc.TableDefs.Refresh > On Error GoTo ErrLink > Set tDef = New TableDef > tDef.name = sLinkTName > tDef.Connect = "Text;DSN=" + sSpecification + > ";FMT=Fixed;HDR=NO;IMEX=2;Database=" + GetPath(sSourceFName) > tDef.SourceTableName = GetName(sSourceFName) + "." + > GetExt(sSourceFName) > DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > sSourceFName, False > dbc.TableDefs.Append tDef > AddLinkTableTXT = True > Exit Function > ErrLink: > AddLinkTableTXT = False > Exit Function > End Function ************************************************************************ - Hide quoted text - - Show quoted text - Quote: > ***************************** > Each time I'm getting False as a result because of error : > 3625 > The text file specification 'Account_PP_Link_Specification' does not exist. > You can't import, export, or link using the specification. > BUT: Specification 'Account_PP_Link_Specification' does exist > !!!!!!!!!!!!!!! > (I've checked it in system table). > And I can upload this ... file by command: > 'DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > sSourceFName, False' > which works just perfect! But I do not want to do this! > After link any text file "by hand" everything is works! Text file links by > this function without any problem. > Any solutions? HELP !!!!!!!!!!!
Sat, 11 Oct 2003 03:35:22 GMT

Andr #3 / 5	Linking Text File in DAO - does not work. Same thing.: "The text file specification 'Account_PP_Link_Specification' does not exist." :( Quote: > Try to use > tDef.SourceTableName = GetName(sSourceFName) + "#" + > GetExt(sSourceFName) > Alex > > There is exist function: > > '********************************************** > > ' Return True if link was created seccsessfully > > '******************************************** > > Public Function AddLinkTableTXT(ByVal sLinkTName As String, ByVal > > sSourceFName As String, ByVal sSpecification As String) As Boolean > > ' sLinkTName - Name of table in curent DB > > ' sSourceTName - real table name (from NameMDB) > > ' NameMDB - filename of DB where the sSourceTName is. > > Dim tDef As TableDef > > Dim dbc As Database > > Set dbc = CurrentDb > > On Error Resume Next > > dbc.TableDefs.Delete (sLinkTName) > > dbc.TableDefs.Refresh > > On Error GoTo ErrLink > > Set tDef = New TableDef > > tDef.name = sLinkTName > > tDef.Connect = "Text;DSN=" + sSpecification + > > ";FMT=Fixed;HDR=NO;IMEX=2;Database=" + GetPath(sSourceFName) > > tDef.SourceTableName = GetName(sSourceFName) + "." + > > GetExt(sSourceFName) > > DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > > sSourceFName, False > > dbc.TableDefs.Append tDef > > AddLinkTableTXT = True > > Exit Function > > ErrLink: > > AddLinkTableTXT = False > > Exit Function > > End Function ************************************************************************ - Hide quoted text - - Show quoted text - Quote: > > ***************************** > > Each time I'm getting False as a result because of error : > > 3625 > > The text file specification 'Account_PP_Link_Specification' does not > exist. > > You can't import, export, or link using the specification. > > BUT: Specification 'Account_PP_Link_Specification' does exist > > !!!!!!!!!!!!!!! > > (I've checked it in system table). > > And I can upload this ... file by command: > > 'DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > > sSourceFName, False' > > which works just perfect! But I do not want to do this! > > After link any text file "by hand" everything is works! Text file links > by > > this function without any problem. > > Any solutions? HELP !!!!!!!!!!!
Sat, 11 Oct 2003 04:15:14 GMT

Phillip Carte #4 / 5	Linking Text File in DAO - does not work. Andre Try renaming the specification to something shorter with the underscore characters. Phillip Carter Quote: > There is exist function: > '********************************************** > ' Return True if link was created seccsessfully > '******************************************** > Public Function AddLinkTableTXT(ByVal sLinkTName As String, ByVal > sSourceFName As String, ByVal sSpecification As String) As Boolean > ' sLinkTName - Name of table in curent DB > ' sSourceTName - real table name (from NameMDB) > ' NameMDB - filename of DB where the sSourceTName is. > Dim tDef As TableDef > Dim dbc As Database > Set dbc = CurrentDb > On Error Resume Next > dbc.TableDefs.Delete (sLinkTName) > dbc.TableDefs.Refresh > On Error GoTo ErrLink > Set tDef = New TableDef > tDef.name = sLinkTName > tDef.Connect = "Text;DSN=" + sSpecification + > ";FMT=Fixed;HDR=NO;IMEX=2;Database=" + GetPath(sSourceFName) > tDef.SourceTableName = GetName(sSourceFName) + "." + > GetExt(sSourceFName) > DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > sSourceFName, False > dbc.TableDefs.Append tDef > AddLinkTableTXT = True > Exit Function > ErrLink: > AddLinkTableTXT = False > Exit Function > End Function ************************************************************************ - Hide quoted text - - Show quoted text - Quote: > ***************************** > Each time I'm getting False as a result because of error : > 3625 > The text file specification 'Account_PP_Link_Specification' does not exist. > You can't import, export, or link using the specification. > BUT: Specification 'Account_PP_Link_Specification' does exist > !!!!!!!!!!!!!!! > (I've checked it in system table). > And I can upload this ... file by command: > 'DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > sSourceFName, False' > which works just perfect! But I do not want to do this! > After link any text file "by hand" everything is works! Text file links by > this function without any problem. > Any solutions? HELP !!!!!!!!!!!
Sat, 11 Oct 2003 18:25:41 GMT

Mark #5 / 5	Linking Text File in DAO - does not work. I also had problems with link specs (although not this one) and turned to opening the CSV as a recordset instead. May be more flexible, depending what you're doing with the data. You can execute the select statement in code to open a recordset or save it as a query; "SELECT * FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=" & Path(strFilePath) & ";].[" & strCSVFileName & "#csv];" The Schema.ini file controls the sort of things the link specifications do with TransferDatabase but has the benefit of being an external text file you can edit. Search the MS KB for Schema.ini for details. HTH, Mark P Quote: > There is exist function: > '********************************************** > ' Return True if link was created seccsessfully > '******************************************** > Public Function AddLinkTableTXT(ByVal sLinkTName As String, ByVal > sSourceFName As String, ByVal sSpecification As String) As Boolean > ' sLinkTName - Name of table in curent DB > ' sSourceTName - real table name (from NameMDB) > ' NameMDB - filename of DB where the sSourceTName is. > Dim tDef As TableDef > Dim dbc As Database > Set dbc = CurrentDb > On Error Resume Next > dbc.TableDefs.Delete (sLinkTName) > dbc.TableDefs.Refresh > On Error GoTo ErrLink > Set tDef = New TableDef > tDef.name = sLinkTName > tDef.Connect = "Text;DSN=" + sSpecification + > ";FMT=Fixed;HDR=NO;IMEX=2;Database=" + GetPath(sSourceFName) > tDef.SourceTableName = GetName(sSourceFName) + "." + > GetExt(sSourceFName) > DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > sSourceFName, False > dbc.TableDefs.Append tDef > AddLinkTableTXT = True > Exit Function > ErrLink: > AddLinkTableTXT = False > Exit Function > End Function ************************************************************************ - Hide quoted text - - Show quoted text - Quote: > ***************************** > Each time I'm getting False as a result because of error : > 3625 > The text file specification 'Account_PP_Link_Specification' does not exist. > You can't import, export, or link using the specification. > BUT: Specification 'Account_PP_Link_Specification' does exist > !!!!!!!!!!!!!!! > (I've checked it in system table). > And I can upload this ... file by command: > 'DoCmd.TransferText acImportFixed, sSpecification, sLinkTName, > sSourceFName, False' > which works just perfect! But I do not want to do this! > After link any text file "by hand" everything is works! Text file links by > this function without any problem. > Any solutions? HELP !!!!!!!!!!!
Fri, 17 Oct 2003 22:00:53 GMT

Page 1 of 1

[ 5 post ]