Why do I get errors while linking ?

[titli]

I am repeatedly getting errors , while trying to link tables in a local database at the below code

If TableExists(CurrentDb.Name, "tblBC") Then DoCmd.DeleteObject acTable, "tblBC"


DoCmd.TransferDatabase acLink, "Microsoft Access", sNormalisedDatabasePath, acTable, "tblBC", "tblBC" ----getting error in this line

The database was built in Access 2003 but now I am trying to run it in Access 2007.

The value of the normalised database is :---
?sNormalisedDatabasePath
C:\Documents and Settings\All Users\Documents\GIR_BE.mdb


The error message which I get doesn't have any error description.From the line which throws the error as shown above,the control moves to the UserLog subroutine , it writes down the status and then mssgbox err.description , which doesn't show any error description.And then there is the Resume statement and it keeps repeating.

I have no clue , why am I getting this error.
GIR_BE.mdb is at C:\Documents and Settings\All Users\Documents\

DoCmd.TransferDatabase acLink, "Microsoft Access", sNormalisedDatabasePath, acTable, "tblBC", "tblBC" (ERROR IN THIS LINE)


acLink--(not supported for .adp databases)..Can this be a reason ??But GIR_BE.mdb

is a .mdb




Thanks
Titli

[ADezii]

Need to see the Code behind TableExists(). As an alternative approach:

Dim tdf As DAO.TableDef
 
For Each tdf In CurrentDb.TableDefs
  If tdf.name = "tblBC" Then
    CurrentDb.TableDefs.Delete "tblBC"
      Exit For
  End If
Next

[Lysander]

I have a routine that drops all links to attached tables, then re-creates them for a database on another hard drive. If this routine fails, or I repeat it quickly twice in succession, I get a shed load of errors relating to tables with wierd tmp names. I think when you delete an object in access, it does not get deleted instantly, there seems to be a temp object created, which deletes later.

After your docmd.deleteobject, try putting a DoEvents which will allow Access to catch up and finish the delete before the import starts.

[titli]

TableExists Code

Private Function TableExists(sAccessPath As String, sTable As String) As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = OpenDatabase(sAccessPath)

For Each tdf In db.TableDefs

If tdf.Name = sTable Then
TableExists = True
Exit Function
End If

Next

TableExists = False

End Function
---------------------------------------------------------
sAccessPath - C:\Documents And Settings\All Users\
sTable - tblBC

Have printed sTable and tdf.Name
and match was not found.So TableExists remained False

And the deletion part of the code did not execute at all.
Then comes ' DoCmd.TransferDatabase acLink ' command which throws error

This is the case when i run in 2007.
-------------------------------------------------------------------------
*******Complete separate problem when run in 2003 ******

MDB is actually a 2003 one.

Whereas in 2003 the problem is completely different , the application fails at the first step with an error , The file at 'some path ' is opened by another user !The file must be closed before dat acan be imported.Find teh user and try again in 5 mins.

But the file is not opened by anyone.

It fails after we Determine what type of transfer we need to perform ------
Select Case m_ImportDetails.m_TransferType

and transfer type was TransferText ------
Case 5 'TransferText
.TransferText m_ImportDetails.m_TransferType, m_ImportDetails.m_SpecName, m_ImportDetails.m_LocalTableName, _
m_ImportDetails.m_FilePath, m_ImportDetails.m_FieldNames
(all of these have values)

after that it moves to error handler-----
Case 3051
MsgBox "The file: " & vbCrLf & m_ImportDetails.m_FilePath & " is open by another user!" & Chr(13) _
& vbCrLf & "This file must be closed before data can be imported. Find the user or try again in 5 minutes.", vbCritical, "Data not Imported!"
GoTo Exit_fLinkFile:

I am not able to open the file in the path mentioned above(some path).It says either teh file is read-only or encrypted.(.csv file , 13, 711 KB)

But why is someone else able to run the application in their system ?

I read this somewhere :--
There can be a variety of causes for 3051 errors when creating .csv files. They seem to include these points , how do i check if the reason is point 3 below?

-Insufficient permissions on the folder where the file is being created
-A non-standard file extension (if you're using something other than
.txt or .csv, try changing it)
-The file already exists and has been locked by another process (or, in
one case i saw, by your own application)
-Maybe, the file is read-only.



Thanks for your replies , would be grateful if you can spend some more time , telling me what could be the reason of the error and a probabale solution would be of great help !!

[ADezii]

Const conDB_PATH As String = "C:\Test\Test.mdb"
Const conTABLE_NAME As String = "tblTest"
 
 
If TableExists(conDB_PATH, conTABLE_NAME) Then DoCmd.DeleteObject acTable, conTABLE_NAME
 
DoCmd.TransferDatabase acLink, "Microsoft Access", conDB_PATH, acTable, _
                       conTABLE_NAME, conTABLE_NAME, False

Public Function TableExists(sAccessPath As String, sTable As String) As Boolean
Dim tdf As DAO.TableDef
 
For Each tdf In CurrentDb.TableDefs
  If tdf.name = sTable Then
    TableExists = True
      Exit Function
  End If
Next
 
TableExists = False
End Function

[Lysander]

Your Access 2003 error message might be a clue.

In your tableexists function you have the line

Set db = OpenDatabase(sAccessPath)

but you never close the database or set db=nothing.

ADezii's function above does not do an open on the database and could fix the problem.

[titli]

Why am I able to open ceratin .csv files in Excel 2007 and and use the files for running an 2007 MS access application , whereas not able to open same .csv in Excel 2003 and not able to use the files for the MS Access application (access 2007 ) ? ---- I need help !! Please.I have to run it 2003.And it's kind of very urgent.