469,127 Members | 1,255 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

Why do I get errors while linking ?

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 :---
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

Feb 15 '11 #1
6 2146
8,800 Expert 8TB
Need to see the Code behind TableExists(). As an alternative approach:
Expand|Select|Wrap|Line Numbers
  1. Dim tdf As DAO.TableDef
  3. For Each tdf In CurrentDb.TableDefs
  4.   If tdf.name = "tblBC" Then
  5.     CurrentDb.TableDefs.Delete "tblBC"
  6.       Exit For
  7.   End If
  8. Next
Feb 15 '11 #2
344 Expert 100+
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.
Feb 15 '11 #3
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


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 !!
Feb 16 '11 #4
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Const conDB_PATH As String = "C:\Test\Test.mdb"
  2. Const conTABLE_NAME As String = "tblTest"
  5. If TableExists(conDB_PATH, conTABLE_NAME) Then DoCmd.DeleteObject acTable, conTABLE_NAME
  7. DoCmd.TransferDatabase acLink, "Microsoft Access", conDB_PATH, acTable, _
  8.                        conTABLE_NAME, conTABLE_NAME, False
Expand|Select|Wrap|Line Numbers
  1. Public Function TableExists(sAccessPath As String, sTable As String) As Boolean
  2. Dim tdf As DAO.TableDef
  4. For Each tdf In CurrentDb.TableDefs
  5.   If tdf.name = sTable Then
  6.     TableExists = True
  7.       Exit Function
  8.   End If
  9. Next
  11. TableExists = False
  12. End Function
Feb 16 '11 #5
344 Expert 100+
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.
Feb 16 '11 #6
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.
Feb 17 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Joonas Paalasmaa | last post: by
3 posts views Thread by rama | last post: by
reply views Thread by SM | last post: by
reply views Thread by Chris | last post: by
1 post views Thread by dasilva109 | last post: by
2 posts views Thread by Angus | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.