• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 843
  • Last Modified:

ISAM error importing SQL table into Access 2003

I am trying to import SQL tables into my Access 2003 DB. I am using a macro to do this.  When I run the macro, I get the error "Could not find installable ISAM" .  From looking at previous ISAM questions, most were having problems in VB, not macros.
I am using the "TransferDatabase" action with type set to "ODBC Database", full path of DBF file.  I have tried  map drive and UNC naming.
What am I missing?
Do I need to build a Module?  If so, some help on it would be appreciated.  Iv'e never built one...
thank you...
0
rwarren99
Asked:
rwarren99
  • 11
  • 10
  • 5
  • +2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say SQL tables, do you mean SQL Server?
0
 
Pigster14IT ConsultantCommented:
Well I do not have Access 2003. We are on 2002. However, I have had one database that when ran on a different machine will give this error.
The fix that I know of is to actually re-install Access on the machine. So that might be an option, but you may want to see what other suggestions flow in.

Is this a new thing...or has it been running awhile and all of a sudden broke?
My issue was that the db was fine and then just one day someone else needed to run it on another machine and that's where we got the error.

Thanks.
0
 
rockiroadsCommented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rockiroadsCommented:
Actually come to think of it, not sure if this will apply to A2003

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note that, during the install of Access, you can select to install drivers for different file formats ... if your particular copy of Access doesn't have those ISAM drivers installed, you'll get these messages. Only way I know of to do this is to re-install Access, selecting the Maintenance or "Add Other Components" option when prompted.
0
 
Pigster14IT ConsultantCommented:
0
 
rockiroadsCommented:
ok, this is for A2000

http://support.microsoft.com/kb/209805/EN-US/

Man, I even saw a link for A v2 so how old is this issue!
0
 
rockiroadsCommented:
Ok, for Access2003

Has this DB been upgraded from an earlier version?

this is the only thing I found on MSDN regarding Access2003
http://support.microsoft.com/kb/310937/en-us

0
 
rwarren99Author Commented:
In answer to some the questions.
1]  yes I am hitting an  SQL Server.
2] Don't remember wha tI installed with the defaoult install.  I know I selected everything but other lanuages.
3] I compare the MS tech note to the reg...  Thesy look good...

Am I using the right statement?  "TransferDatabase".  In my example, I have the DB name set to: \\hrsecurity\Program Files\Microsoft SQL Server\MSSQL\Data\ckpt.mdf.

I also tried drive map and received the same error.
In the macro field (fro TransferDatabase) I have the following:
Tranfer type: Import
DB Type: ODBC Database
DB Name: \\hrsecurity\Program Files\Microsoft SQL Server\MSSQL\Data\ckpt.mdf
Opject type: table
Source: dbo_cardholder (table in ckpt DB)
destination: dbo_cardholder_new (name to call the imported table)
Structure Only: No
 
Hope this helps...
thanks all.....
0
 
Pigster14IT ConsultantCommented:
Everything appears to be correct. One final thing you may try, I am sure it will fail, but right click in the Table area in the white space and try to link that way. I assume you will get the same thing.

I think you will have to re-install.

Thanks.
0
 
rwarren99Author Commented:
Again.. I am trying to automate the import process using macros...   I can manually import the tables...  The problem arises when I attempt to use a macro to import the table(s).   Before I reinstall, is the syntax correct for the macro?.  
Will the reinstall cause me to register my Office 2003 with MS again??
Thanks…
0
 
Pigster14IT ConsultantCommented:
Okay well interesting, you can manually link the table? I was expecting that to fail....

Well, I will try to set up a macro to verify..might be a bit later.

Thanks.
0
 
rwarren99Author Commented:
Thanks....
0
 
Pigster14IT ConsultantCommented:
Okay, set up a macro as you showed and received the same message.
I tried one manually and was fine.

So I did the same code in VBA:

DoCmd.TransferDatabase acImport, "ODBC Databases", [dbname], acTable, [tablename],[destinationtablename]

Received the following error:
The ODBC Databases type isn't an installed database type or doesn't support the operation you chose.

I also tried the link option, but same error.

I can not think right off of an example I have here where we link or import a table from SQL in code. I'm sure it can be done....okay will continue to search.

Thanks.






0
 
rwarren99Author Commented:
It sounds like this is what I need.  Due to limited knowledge of  Access’ extra abilities, I could use some assistance with the code.  Everything I have ever done was in macros and the built in functions…  Importing txt files work fine using macros. It sounds like the code requires the use of Modules which I have never done.  Can you assist in setting up the format in a module?
Do you know of a good reference source?

My Data Source Name is: ckpt, DB: SQLckpt and table is: employees.
The code from the link is:
DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=DSNName;UID=xxx;PWD=xxx;LANGUAGE=us_english;" _
        & "DATABASE=DBName", acTable, "tbl_yourtable", "tbl_newTableName"

I am not sure how you are using acImport, acTable, tbl_yourtable, or tbl_newTableName.   Is the tbl_newTableName only needed if importing to a new table instead of over writting an existing table?
Here is what I see:
DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=ckpt;UID=xxx;PWD=xxx;LANGUAGE=us_english;" _
        & "DATABASE=SQLckpt", acTable, "tbl_yourtable", "tbl_newTableName"

Thanks…
0
 
Pigster14IT ConsultantCommented:
DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=ckpt;UID=xxx;PWD=xxx;LANGUAGE=us_english;" _
        & "DATABASE=SQLckpt", acTable, "employees", "tbl_newTableName"

You are on the right track it appears. The tbl_yourtable I replaced with employees. This is your source table name.

Now the tbl_newTableName is yours to choose. This is the table name that will be given to the table when you see it in your db. You can keep it the same or change it.

Does your employees table from sql look like dbo_employees. If so, you'll have to change it to what it looks like exactly in sql. But if not, then keep it as is.

Try that, let me know if you need anything else.

Thanks.
0
 
rwarren99Author Commented:
OK...  did i mention be when it comes to anything outside of the built in functions of Access, I'm a rookie...
I tried to add the code to a the TransferSQLDatabase command in the macro with no luck...
I received error: "command or actin 'TransfersSQLDatabase' isn't available now...."
How or where do I setup the code?
When I connect to the DB manually, I do not require a user name or PW.  Can that be omitted from the DoCmd string?
thanks...
0
 
Pigster14IT ConsultantCommented:
Well you should not be using TransferSQLDatabase, but the TransferDatabase function that is listed above.

DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=ckpt;LANGUAGE=us_english;" _
        & "DATABASE=SQLckpt", acTable, "employees", "tbl_Employee"

So if you copy this in, does it work? If not, give the specific error.

Thanks.

0
 
rwarren99Author Commented:
Sorry.. Missed it..
I cut and pasted the full DOcmd string into the Database name of the macro for action "transferdatabase".
The entries are as followered:
Transfer type: import
DB type: ODBC database
DB name:DoCmd.TransferDatabase acImport, "ODBC Database",  "ODBC;DSN=ckpt;LANGUAGE=us_english;" _
        & "DATABASE=SQLckpt", acTable, "employees", "tbl_Employee"
Source: employees
Destination: tbl_Employee
Structure only: no
RESULTS...........
Error.  Could not find installed ISAM.
I know I doing someting wrong....
0
 
Jim P.Commented:
Dumb question, why don't you just permanently link the table and just do an append/make table query to your local tables?
0
 
rwarren99Author Commented:
It's a DB I do not want to permanently be connected to.....
It's not a big thing to continue linking the tables when I need too.  I wanted to simplified the process by automating it as much as I could.   But for the time spent (yours and mine), I'm thinking it is not worth any additional efforts right now...
thank you for your assistance....
0
 
Pigster14IT ConsultantCommented:
Well, I have a sample of code I had found and I will not have time today and will not be here next week, I had asked a friend of mine to help you with the code. But it is sort of complicated, especially if you are not familiar with the modules.

I will leave it up as to what you want to do with this question.

Thanks.
0
 
Pigster14IT ConsultantCommented:
My e-mail is slow....missed the last two posts...
0
 
rwarren99Author Commented:
I'm willing to learn...
Have your friend give it a shot.... I'm in no hurry...  It' more of a challenge now......
0
 
Pigster14IT ConsultantCommented:
well, the code is going to link the table in....unless you delete the table after you use it. Which is sort of silly. I mean is there anything specific that is causing you problems that you can not have a linked table in the db? And if not, then I would say your best route is just to link it and go from there. You can copy the data into a temp table or something if you would rather work with the copy data rather than the linked.

Thanks.

0
 
Jim P.Commented:
This is a multiple function/module approach to do this -- I dug it out of the M$ Knowledege base a while back. Note that this will leave the userid and password in plain text in your database and assumes you are using mixed mode authentication.  

The first chunk is to build a table to hold information about the server/db/table(s) that you want to link.
---------------------------------------------------------------------------------
Public Function Build_ODBC_Table()

Dim DB As Database
Dim TableName As TableDef
Dim FieldName As Field
Dim FieldProperty As Property

If DoesTblExist("tblODBCDataSources") = False Then
    Set DB = CurrentDb()
    Set TableName = DB.CreateTableDef("tblODBCDataSources")
   
    With TableName
        .Fields.Append .CreateField("DatabaseName", dbText, 50)
        .Fields.Append .CreateField("UID", dbText, 50)
        .Fields.Append .CreateField("PWD", dbText, 50)
        .Fields.Append .CreateField("Server", dbText, 50)
        .Fields.Append .CreateField("ODBCTableName", dbText, 50)
        .Fields.Append .CreateField("LocalTableName", dbText, 50)
        .Fields.Append .CreateField("DSN", dbText, 50)
        .Fields.Append .CreateField("DSN_Desc", dbText, 50)
    End With
   
    DB.TableDefs.Append TableName
   
    With TableName
        .Fields("DatabaseName").AllowZeroLength = True
        .Fields("UID").AllowZeroLength = True
        .Fields("PWD").AllowZeroLength = True
        .Fields("Server").AllowZeroLength = True
        .Fields("ODBCTableName").AllowZeroLength = True
        .Fields("DSN").AllowZeroLength = True
        .Fields("DSN_Desc").AllowZeroLength = True
    End With
End If

End Function
0
 
Jim P.Commented:
Add the above function into a module and then execute it.  It will create the table "tblODBCDataSources".  The idea being that after the table is created you go populate the table with the information about what tables you want to link.  Currently you only have one table in one DB that you want to link.  But if you end up with more linke tables you can just add them.
0
 
Jim P.Commented:
After you have populated the table above with the correct information you can put these function in to actually do the link.  Note that if you don't want to keep the username and password in the database we can code around it but it can be a pain.

--------------------------------------------------------------------------------
Public Function CreateODBCLinkedTables() As Boolean
'On Error GoTo CreateODBCLinkedTables_Err
   
Dim strTblName As String
Dim strConn As String
Dim DB As Database
Dim RS As Recordset
Dim tbl As TableDef

Set DB = CurrentDb
Set RS = DB.OpenRecordset("tblODBCDataSources")

    With RS
       While Not .EOF
          ' ---------------------------------------------
          ' Link table
          ' ---------------------------------------------
          strTblName = RS("LocalTableName")
          strConn = "ODBC;"
          strConn = strConn & "DSN=" & RS("DSN") & ";"
          strConn = strConn & "APP=Microsoft Access;"
          strConn = strConn & "DATABASE=" & RS("DatabaseName") & ";"
          strConn = strConn & "UID=" & RS("UID") & ";"
          strConn = strConn & "PWD=" & RS("PWD") & ";"
          strConn = strConn & "TABLE=" & RS("ODBCTableName")
          If (DoesTblExist(strTblName) = False) Then
             Set tbl = DB.CreateTableDef(strTblName, _
                           dbAttachSavePWD, RS("ODBCTableName"), _
                           strConn)
             DB.TableDefs.Append tbl
          Else
             Set tbl = DB.TableDefs(strTblName)
             tbl.Connect = strConn
             tbl.RefreshLink
          End If

          RS.MoveNext
       Wend
    End With
    CreateODBCLinkedTables = True
    'MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
    Exit Function
CreateODBCLinkedTables_Err:
    MsgBox Err.Description, vbCritical, "MyApp"
    Resume CreateODBCLinkedTables_End

End Function
-----------------------------------------------------------------
   '***************************************************************
      'The DoesTblExist function validates the existence of a TableDef
      'object in the current database. The result determines if an
      'object should be appended or its Connect property refreshed.
      '***************************************************************

Public Function DoesTblExist(strTblName As String) As Boolean
    On Error Resume Next
    Dim DB As Database, tbl As TableDef
    Set DB = CurrentDb
    Set tbl = DB.TableDefs(strTblName)
    If Err.NUMBER = 3265 Then   ' Item not found.
       DoesTblExist = False
       Exit Function
    End If
    DoesTblExist = True
End Function
0
 
rwarren99Author Commented:
thanks...
I'll look it over when I get a minute but this week is going to busy.......
0
 
rwarren99Author Commented:
I was able to make some head way with the code to get certain control over the inport.  It will take me more time (none right now) but now I have a path to following.
thanks all.....
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 11
  • 10
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now