Run-time error '3251' - While trying to link a table in VB6

[richardhodge]

I am a VB6 database programmer and have run into a small problem. The company I work for primarily uses Microsoft Access 2000 for the database that is the back end for our software. Well the functionality that I am currently working on creates a table in one database and then links it into the primary database that is used by the program to store and retrieve data from.

Here's the problem. I can create the table fine but when I go to link the table I get a run time error as follows:
Run-time error '3251': Operation is not supported for this type of object.

I have looked all over the internet to find a solution to this problem but can't seem to figure it out or figure out a better way of accomplishing what I am trying to do. I'll go ahead and post my code and hopefully someone has an idea on what to fix.

VB6 Code:::::::

Private Function createlink()
    Dim dbstemp As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbstemp = DBEngine.OpenDatabase(LinkDB.Name)
    On Error Resume Next
    Set tdf = dbstemp.TableDefs(Name)
    If Err.Number <> 0 Then
        On Error GoTo 0
        'the link does not exist yet so create it
        Set tdf = dbstemp.CreateTableDef("ZZZZtesting")
        'Create tabledef defining the link to the table we want
        tdf.Connect = ";DATABASE=" & dpc.DBPath & "\cinfoEIRS.mdb"
        'name of the table in the source DB
        tdf.SourceTableName = "ZZZZtesting"
        'Add the tabledef to the link database
        'On Error Resume Next
        dbstemp.TableDefs.Append (tdf)
        dbstemp.TableDefs.Refresh
    End If
    On Error GoTo 0
 
    Set dbstemp = Nothing
    Set tdf = Nothing
End Function

DBengine is a global variable that points to our workspace.
LinkDB is a global variable that points to the main database that has all of the links in it.
dpc.DBPath is a global class that has the path for all databases

things that I have tried that have not worked:
- put all attributes for the tabledef into one createTableDef command
- creating a new table def and then try appending
- create a new workspace
- Ignoring the error

Everything except ignoring the error breaks at the same point, at the command:
dbstemp.TableDe fs.Append (tdf)
And it is always the same error 3251.

Well if anyone has any insight into this I would appreciate it.

[willakawill]

Although I never use dao or tabledef there is something about this code that does not look right to me. I think you would normally create a tabledef object and then append it to the database. After that you can set its properties (see here). I don't think you can set the tabledef to an existing object and then append it again.

Also your function does not have a return type or value.

[Killer42]

Got it!

Change
dbstemp.TableDe fs.Append (tdf)
to
dbstemp.TableDe fs.Append tdf

[vijaydiwakar]

I am a VB6 database programmer and have run into a small problem. The company I work for primarily uses Microsoft Access 2000 for the database that is the back end for our software. Well the functionality that I am currently working on creates a table in one database and then links it into the primary database that is used by the program to store and retrieve data from.

Here's the problem. I can create the table fine but when I go to link the table I get a run time error as follows:
Run-time error '3251': Operation is not supported for this type of object.

I have looked all over the internet to find a solution to this problem but can't seem to figure it out or figure out a better way of accomplishing what I am trying to do. I'll go ahead and post my code and hopefully someone has an idea on what to fix.

VB6 Code:::::::

Private Function createlink()
    Dim dbstemp As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbstemp = DBEngine.OpenDatabase(LinkDB.Name)
    On Error Resume Next
    Set tdf = dbstemp.TableDefs(Name)
    If Err.Number <> 0 Then
        On Error GoTo 0
        'the link does not exist yet so create it
        Set tdf = dbstemp.CreateTableDef("ZZZZtesting")
        'Create tabledef defining the link to the table we want
        tdf.Connect = ";DATABASE=" & dpc.DBPath & "\cinfoEIRS.mdb"
        'name of the table in the source DB
        tdf.SourceTableName = "ZZZZtesting"
        'Add the tabledef to the link database
        'On Error Resume Next
        dbstemp.TableDefs.Append (tdf)
        dbstemp.TableDefs.Refresh
    End If
    On Error GoTo 0
 
    Set dbstemp = Nothing
    Set tdf = Nothing
End Function

DBengine is a global variable that points to our workspace.
LinkDB is a global variable that points to the main database that has all of the links in it.
dpc.DBPath is a global class that has the path for all databases

things that I have tried that have not worked:
- put all attributes for the tabledef into one createTableDef command
- creating a new table def and then try appending
- create a new workspace
- Ignoring the error

Everything except ignoring the error breaks at the same point, at the command:
dbstemp.TableDe fs.Append (tdf)
And it is always the same error 3251.

Well if anyone has any insight into this I would appreciate it.

Dear why ru using DAO thry to use ADO. Its now the best option databridge

[Killer42]

Dear why ru using DAO thry to use ADO. Its now the best option databridge

That does seem to be the consensus of opinion - that ADO is much superior. On the other hand, if you already have something that works, why change it?

[willakawill]

Dear why ru using DAO thry to use ADO. Its now the best option databridge

I agree with Killer on this. I don't think it is useful to recommend completely changing all of the code just because of a bug in one line.

[vijaydiwakar]

See this is not the question of one line bug
if he use ADO then in future also he is able to get the maximum facility given by the ADO. Programming is not the game of just coading its the game of visualization if ur not able to visualise the problem then its of no use.
If M$ alredy given us such advance utilities then why should we go for older one

[willakawill]

See this is not the question of one line bug
if he use ADO then in future also he is able to get the maximum facility given by the ADO. Programming is not the game of just coading its the game of visualization if ur not able to visualise the problem then its of no use.
If M$ alredy given us such advance utilities then why should we go for older one

Because you never ever have a need to change something that works. In this case a part of the code does not work most likely because of the syntax and not because of the type of objects used.

[Killer42]

Because you never ever have a need to change something that works. In this case a part of the code does not work most likely because of the syntax and not because of the type of objects used.

Yes, I think you have to consider the amount of effort involved in rewriting an entire application, just because there might be some benefit, some time in the future.

[richardhodge]

Well removing the ( ) from around tdf fixed the problem. The strange thing is that I know I tried that before and it still had problems at the exact same location. Oh well it seems to be working now and I have been testing it for over an hour now with no problems.

As far as switching to ADO that is something that will be happening in the next few months. Personally I prefer ADO, but the company has invested so much time into the current product, which was originally written in MS Access not in vb6 that they did not want to switch to ADO until we convert to VB.net this year.

Oh well I guess that is life.

Thanks again for all of the help. I knew it had to be something stupid, but then again the biggest bugs normally are something stupid.