UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating New Tabledef -> "could Not Find Installable Isam", Access 2013    
 
   
MrSiezen
post Dec 5 2018, 06:07 AM
Post#1



Posts: 2,415
Joined: 15-February 05
From: Den Bosch - Netherlands


Hi all,

I'm trying to adjust the connection for a linked table to another database AND other table(name).

As you cannot change it apparantly, I'm removing and adding, but now I get this error message on the last line
error 3170: could not find installable isam
It seems like some version difference issue?
Can I add something else to the string to fix it, or?

PS. I cannot install anything in the environment, not edit the registry...

Thanks!!


CODE
Dim tbd As TableDef
Dim Db As DAO.Database
Set Db = CurrentDb

Db.TableDefs.Delete "currenttable"

Set tbd = Db.CreateTableDef("currenttable")
tbd.Connect = "DATABASE=\\xxxx\xxx.accdb"
tbd.SourceTableName = "tblDifferentTableName"

Db.TableDefs.Append tbd



--------------------
UA Rulez!
Go to the top of the page
 
cheekybuddha
post Dec 5 2018, 06:17 AM
Post#2


UtterAccess VIP
Posts: 10,613
Joined: 6-December 03
From: Telegraph Hill


Hi MrSiezen,

Have you tried adding a field(s) to the new tabledef before appending?

Also, are you sure: >> you cannot change it apparantly << ?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MrSiezen
post Dec 5 2018, 07:38 AM
Post#3



Posts: 2,415
Joined: 15-February 05
From: Den Bosch - Netherlands


Hi Cheeky,

It is a linked table, so how could I add a field?

See this thread, it is said there as well you cannot edit it for an existing table...

http://www.UtterAccess.com/forum/index.php?showtopic=1370871


I actually now copied that exact script from that thread, but it gives the same error...

--------------------
UA Rulez!
Go to the top of the page
 
cheekybuddha
post Dec 5 2018, 07:48 AM
Post#4


UtterAccess VIP
Posts: 10,613
Joined: 6-December 03
From: Telegraph Hill


Compare your new .Connect string with a working linked table

See if this StackOverflow thread helps

--------------------


Regards,

David Marten
Go to the top of the page
 
MrSiezen
post Dec 6 2018, 05:20 AM
Post#5



Posts: 2,415
Joined: 15-February 05
From: Den Bosch - Netherlands


Hi Cheeky,

The links are like I showed in the first post. I tried to add "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\xxxx\xxx.accdb" in the connection string. Also "Provider=Microsoft.Jet.OLEDB.4.0;".
I found the proper links here: https://www.connectionstrings.com/access/

But I keep getting the same message.
I'm lost.


CODE
Dim tbd As TableDef
Dim Db As DAO.Database
Set Db = CurrentDb

Db.TableDefs.Delete "currenttable"

Set tbd = Db.CreateTableDef("currenttable")
tbd.Connect = "DATABASE=\\xxxx\xxx.accdb"
tbd.SourceTableName = "tblDifferentTableName"

Db.TableDefs.Append tbd

--------------------
UA Rulez!
Go to the top of the page
 
cheekybuddha
post Dec 6 2018, 05:43 AM
Post#6


UtterAccess VIP
Posts: 10,613
Joined: 6-December 03
From: Telegraph Hill


The only other solutions I see from Googling seem to be registry related, which you say is not an option.

I regret that I have no more ideas at the moment. shrug.gif

Hopefully someone else will jump in to assist. thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Dec 6 2018, 06:03 AM
Post#7



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Yes you can create/edit & delete linked tables
First open the external database then use the same code you would use to modify a local table

Here's some code to modify an existing linked table using DAO

CODE
Sub AlterLinkedTable()

'Use this code to alter linked tables
On Error GoTo Err_Handler

Dim strPath As String, strPwd As String
Dim db As DAO.Database

Set db=CurrentDb

'Open linked database e.g. leavers tables
strPath = full database path & file name

If strPwd <> "" Then 'password protected database
    Set db = DAO.OpenDatabase(strPath, False, False, ";pwd=" & strPwd)
Else 'no password needed
    Set db = DAO.OpenDatabase(strPath, False, False)
End If

'Example usage
'Adds the text field ZZZ size 5 to the linked table tblGCSEStudentSummary
db.Execute "ALTER TABLE tblGCSEStudentSummary " _
& "ADD COLUMN ZZZ Text(5);"

db.Close

Set db=Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & err.Number & " " & err.Description
    Resume Exit_Handler

End Sub


In addition, Allen Browne has some very useful DAO functions you could look at http://allenbrowne.com/func-DAO.html. Also ADO etc

HTH
This post has been edited by isladogs: Dec 6 2018, 06:06 AM
Go to the top of the page
 
MrSiezen
post Dec 6 2018, 07:01 AM
Post#8



Posts: 2,415
Joined: 15-February 05
From: Den Bosch - Netherlands


Hi isladogs,

Thanks for the input, but that code is for editing the table's columns. But I'm trying trying to edit the connection link of that table.

The strange thing is that Isam should not be needed for relinking access tables:
https://docs.microsoft.com/zh-cn/office/VBA...-isamerror-3170
This post has been edited by MrSiezen: Dec 6 2018, 07:17 AM

--------------------
UA Rulez!
Go to the top of the page
 
isladogs
post Dec 6 2018, 07:52 AM
Post#9



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Yes I'm aware of that. Reading post 3, I thought you wanted to do that as well.

I don't understand why you're getting the installable ISAM message either.
Normally you see that if you try to update a linked Excel table in Access.

Sorry I can't help.
Go to the top of the page
 
JonSmith
post Dec 6 2018, 09:40 AM
Post#10



Posts: 3,980
Joined: 19-October 10



So you can get some subtle differences in versions that cause this error but I've only seen it in Excel. An example is extra parameters that a slightly newer build has that aren't present before. That being said it shouldn't be an issue if you are editing an existing working connection string to an updated one.

It basically means that it doesn't understand what to do with part of the connection string.

I would suggest using debug.print to see what it is trying to change your connection string to. Compare that to the original, perhaps you'll see a fault in your coding. If that doesn't help manually create a new connection to the target database and debug.print its connection string, compare that to the connection string your code generates. At that point you should see the errors.
Go to the top of the page
 
MrSiezen
post Dec 10 2018, 08:53 AM
Post#11



Posts: 2,415
Joined: 15-February 05
From: Den Bosch - Netherlands


So....

tbd.Connect = "DATABASE=\\xxxx\xxx.accdb"

should be

tbd.Connect = ";DATABASE=\\xxxx\xxx.accdb"

and all is happy...

crazy.gif

--------------------
UA Rulez!
Go to the top of the page
 
cheekybuddha
post Dec 10 2018, 09:29 AM
Post#12


UtterAccess VIP
Posts: 10,613
Joined: 6-December 03
From: Telegraph Hill


yayhandclap.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Dec 10 2018, 10:46 AM
Post#13



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Hooray. Those pesky semicolons! Easy to miss!
Go to the top of the page
 
JonSmith
post Dec 10 2018, 10:49 AM
Post#14



Posts: 3,980
Joined: 19-October 10



So yeah, that makes sense. Without that semi colon the parameters get all confused. Glad you were able to fix it. Its always good to compare a valid connection string vs a bad one to pinpoint the issues.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 12:23 AM