Solved

ISAM error importing SQL table into Access 2003

Posted on 2006-06-30
32
808 Views
Last Modified: 2008-03-10
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
Comment
Question by:rwarren99
  • 11
  • 10
  • 5
  • +2
32 Comments
 
LVL 84
ID: 17017326
When you say SQL tables, do you mean SQL Server?
0
 
LVL 8

Expert Comment

by:Pigster14
ID: 17017349
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17017354
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17017368
Actually come to think of it, not sure if this will apply to A2003

0
 
LVL 84
ID: 17017382
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17017387
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17017397
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17017403
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
 

Author Comment

by:rwarren99
ID: 17031331
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17031354
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
 

Author Comment

by:rwarren99
ID: 17031476
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17031522
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
 

Author Comment

by:rwarren99
ID: 17031687
Thanks....
0
 
LVL 8

Expert Comment

by:Pigster14
ID: 17032010
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17032032
0
 

Author Comment

by:rwarren99
ID: 17050122
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 8

Expert Comment

by:Pigster14
ID: 17050192
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
 

Author Comment

by:rwarren99
ID: 17057410
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17057634
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
 

Author Comment

by:rwarren99
ID: 17057859
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 17058129
Dumb question, why don't you just permanently link the table and just do an append/make table query to your local tables?
0
 

Author Comment

by:rwarren99
ID: 17058210
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17058256
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
 
LVL 8

Expert Comment

by:Pigster14
ID: 17058267
My e-mail is slow....missed the last two posts...
0
 

Author Comment

by:rwarren99
ID: 17058298
I'm willing to learn...
Have your friend give it a shot.... I'm in no hurry...  It' more of a challenge now......
0
 
LVL 8

Expert Comment

by:Pigster14
ID: 17058407
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 17058999
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 17059810
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 250 total points
ID: 17059951
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
 

Author Comment

by:rwarren99
ID: 17075386
thanks...
I'll look it over when I get a minute but this week is going to busy.......
0
 

Author Comment

by:rwarren99
ID: 17123147
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 17123199
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now