Help With IF THEN ELSE statment

[EL-g]

Hey Guys,
I'm running a code that imports a few .txt files as linked tables in my access, but i needed to create a IF, THEN, ELSE statement with the condition that follows:

If FileName = Table name from Access
Then go to next loop
Else DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

File Name is already defined and is the name for each text file which is also the same name of each table. I still dont know too how to go to next loop in the THEN statement. Can anyone help me

It should be something like, if table exist then go to next loop

thx in advance

 

[gemma-the-husky]

this function will determine whether a table exists. it tries to retrieve the name of the table passed to the function, by checking the name in the tabledefs collection. If the table exists the function is true, if not, its false

[sorry if your saw previous version - had to trap the error if the table does not exist, as it causes an error ]

Function tableexists(tbl As String) As Boolean
On Error GoTo nofile
tableexists = CurrentDb.TableDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt

exithere:
Exit Function

nofile:
tableexists = False
resume exithere
End Function


so in your loop you can say

If tableexists(FileName) then
go to next loop
Else DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

 

[EL-g]

Function tableexists(tbl As String) As Boolean
On Error GoTo nofile
tableexists = CurrentDb.TableDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt

exithere:
Exit Function

nofile:
tableexists = False
Resume exithere
End Function



Sub import2()
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

FileName = Dir(Path & "*.*")

Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
' Get next file
FileName = Dir
If FileName = "" Then Exit Do

If tableexists(FileName) Then ??
Else: DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

Loop

' Trim array size to actual number of files
ReDim Preserve Files(1 To nFiles)


End Sub

What should i put in the then statement to make it go to next loop

 

[EL-g]

Im trying this code, but for some reason they are all going to both satements THEN and ELSE. I dont know why

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function



Sub a()
Dim result As Integer
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

FileName = Dir(Path & "*.*")


Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
' Get next file
FileName = Dir
If FileName = "" Then Exit Do
If fExistTable(FileName) = True Then GoTo hell Else GoTo heaven
heaven:
DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

hell:

Loop

 

[gemma-the-husky]

change the loop

either

If tableexists(FileName) Then
'you can leave it blank and do nothing
else
DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""
end if

or instead use

If not(tableexists(FileName) ) Then
DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""
end if

but you have also changed the fexists a bit - i don't think you really need to do all that with the fexists test.

 

[EL-g]

Ive tried both of them and for some reasons all the tables that exist already and the ones that dont are both being created again. I dont know why

 

[EL-g]

I think i could solve this problem if i create 2 arrays, one for the true and one for the false. And then make the Docmd only for the false. What do you think

 

[gemma-the-husky]

I've checked your code, and your fexists works ok, but you certainly don't need to check every table to see if the table exists. My original code worked fine

immediately before your heaven: label, insert

MsgBox "Load File " & FileName to see EXACTLY what is happening.

I think the files you are returning from your directory search have an extension eg .xls, and I am sure that Access is not creating the extension in the new table name, hence every time it doesn't find an exact matching table, and creates a new one.

You may need to delete the file extension before you test whether the table exists.

 

[EL-g]

aha. It might be the extension then. My files are all .txt
Then the table in access are filename_txt instead of .txt
That might be the problem. How can i delete the file extension then?
I might need to make a new restriction to the FileName then. Because this is the actual name of each table in access and also the name of the .txt files

 

[gemma-the-husky]

you need to remove the characters from the dot to the end to get the base name of the file,

therefore your filename needs to be

left(filename,instr(1,filename,".")-1)

the instr will return the position of the first instance of a dot/period in your filename. The left will take all the characters BEFORE that position.

 

[EL-g]

Thanks a lot for all the help gemma-the-husky. You really saved me here and i really appreciate all the tips . It works perfect now. I'll post the final code here in case someone need it in the future

Function tableexists(tbl As String) As Boolean
On Error GoTo nofile
tableexists = CurrentDb.TableDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt

exithere:
Exit Function

nofile:
tableexists = False
Resume exithere
End Function
________________________________________________________________________

Sub ImportTxtFiles()
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

FileName = Dir(Path & "*.*")


Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
' Get next file
FileName = Dir
If FileName = "" Then Exit Do
FileName = Left(FileName, InStr(1, FileName, ".") - 1)
If tableexists(FileName) Then Else DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName & ".txt", True, ""

Loop

End Sub

 

[EL-g]

Actually i found a bug. The 1st file is not being uploaded. I dont know why. Im confused. It seems that its looping from the 2nd file in the folder until the last one.

 

[gemma-the-husky]

probably because you are using the dir command outside your loop, and then using it again in the loop BEFORE you save a table.

The easiest way to track what's happening is to put msgbox's in various places to check what yuo are loading and saving , until its all ironed out.

so put filename=dir AFTER the transfertext statement and immediatley before the loop statement

 

[EL-g]

Hey gemma-the-husky. THAT WAS JUST PERFECT!!!!!!!!! Damn you are good (^.^)

Many thanks again you rock!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

And Thanks for the tipo on the MsgBox...ill use that!!!!!!!!!

 

[EL-g]

by the way, is there anyway i can declare in my module code the "Apollo Link Specification1". BEcause im importing the database to vb.NET and i get and error because this is not declared there i guess

 

[gemma-the-husky]

i don't understand - once you have imported the table its an access table governed by the access table spec, and the import spec is immaterial to VB - are you trying to do the import from VB?

 

[EL-g]

No, actually what im tring to do follows:
I dicided to manage the database at access and make the program in VB.NET. So i tried to import the .mdb file into VB.Net using ADO.NET. IT worked fine, i can see all the tables in my database at VB.NET, but when i try to access an specific table i get an error message saying that it cannot find the spec. So i thought maybe i should declare the spec at the code so i wouldt have this problem in VB.

What do you think?

 

[gemma-the-husky]

the import export specs are held as a separate object within the dbs. you can export them to another dbs. I am not expert in vb, so can't help with regard to that.

 

[EL-g]

oh ok...........Because basically now it works perfect the auto update and importing from the text files. Ill try to find out some way out of this to import it into VB, or maybe, even think about another program that could suite me. In case i want to join all the tables i created into one do you know if i can make a SQL statement with loops to create a table. Because ive managed to create a table with a query, but i have to type in each table and column in the query. But when another table is created from the auto uptade macro (module we've been talking about) i wozld have to go to my query and type in the info of the new table. Do you know if by any chance i could loop this in a module.
gemma-the-husky thx a lot again for the many helps and replies, and i promise this will be my last question and that ill try not to bother you guys anymore

 

[EL-g]

Ok. I managed to solve the import problem. Aparently i have to copy the linked table and copy as a non linked one. The problem to Make a dynamic SQL statement is still on