CODEWITHSUNDEEP
databasevbams-accessvb6ms-access-2003
tobriand
tobriand

Reputation: 1167

Error 3045 or 3356 when appending a tabledef in Access

I'm building an temporary backend for a snapshot style database using MS Access 2003 (nothing else available), which involves adding some linked tabledefs to the temporary backend. The code has been working for around 3 weeks now, but as of earlier this afternoon, started to throw 3356 (i.e. User X on machine Y already has the database open in exclusive mode...) or 3045 (roughly, unable to open database in exlcusive mode), depending on whether I already establish a connection in Access.

The erroring code is roughly (somewhat trimmed):

Private Sub AddTabledefToDb(dbC As DAO.Database, dbTarget As DAO.Database, strLinkedName As String)
    Dim strPath As String, tdfLinked As DAO.TableDef
    strPath = strGetPathFromConnect(tdfLinked.Connect)

    Set tdfLinked = dbC.TableDefs(strLinkedName)

    ' With the lines below, error thrown is 3356; without 3045 '
    Dim dbLinkedTableIn As DAO.Database
    Set dbLinkedTableIn = Application.DBEngine.Workspaces(0).OpenDatabase(strPath, False, True, tdfLinked.Connect)

    Dim tdfNew as DAO.TableDef
    Set tdfNew = dbTarget.CreateTableDef(Attributes:=dbAttachedTable)
    tdfNew.Name = tdfLinked.Name
    tdfNew.SourceTableName = tdfLinked.SourceTableName
    tdfNew.Connect = tdfLinked.Connect
    dbTarget.TableDefs.Append tdfNew ' Throws 3045 without the lines above or 3356 with them ' 

    ' If needed... ' 
    dbLinkedTableIn.Close
    Set dbLinkedTableIn = Nothing

End Sub

I've a suspicion that the reason for this may be related to a message that displays if I open the database housing the table I'm linking to directly, i.e. that it's only available in Read Only mode (which I'm fairly sure wasn't the case previously). However, I'm unclear why my code requires anything MORE than read only access, and I can't work out why it's trying to get it (especially when I explicitly open the database beforehand in read only mode).

Any help would be very gratefully appreciated.

Thanks

Upvotes: 1

Views: 1609

Answers (1)

tobriand
tobriand

Reputation: 1167

Think I've come across the answer: don't use DAO, use ADO instead. See very roughly below. At the moment, I've not set the Mode property to Read, but initial tests suggest it does at least function without doing so.

Dim cn As ADODB.Connection
Dim tbl as ADOX.Table
Dim cat as ADOX.Catalog

Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open dbTarget.Name ' Path of the db the linked table needs to end up in'

Set cat = New ADOX.Catalog
cat.ActiveConnection = cn

Set tbl = New ADOX.Table
Set tbl.ParentCatalog = cat

tbl.Name = tdfLinked.Name
tbl.Properties("Jet OLEDB:Link Datasource") = strGetPathFromConnectString(tdfLinked.Connect)
tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access"  ' If Password protected, details go here ' 
tbl.Properties("Jet OLEDB:Remote Table Name") = tdfLinked.SourceTableName
tbl.Properties("Jet OLEDB:Create Link") = True

cat.Tables.Append tbl

cn.Close
Set tbl = Nothing
Set cat = Nothing
Set cn = Nothing

Very roughly, it looks like ADO is happy to create a linked table without acquiring read/write access in Code, whereas DAO is not.

Upvotes: 1

Related Questions