ODBC Too many indexed (1 Viewer)

Kenln · Feb 22, 2007

I am trying to like to a table in an AS400 and I am getting the message:

"The operation failed. There are too many indexes on table. Delete some of the indexes on the table and try the operation again."

1) I know the structure of the table, i.e. Name, Fields, etc...
2) I do not need all of the items in the table
3) This will be a read only connection to I am not really worried about indexs being compete

4) How do I see/get/read the information from the table I need?????

Please help

Kenln · Feb 22, 2007

Another question:
Can I create a query (using VBA if needed) that only address the fields I need or create a link to the table using only the fields I need?

Bodisathva · Feb 22, 2007

Create a pass through query so the server carries the load that Access is unable to handle, the server will then return only the requested records. The speed of this approach will depend heavily on server usage.

Kenln · Feb 22, 2007

Awesome!!!

It took me a couple of tries to figure it out but Thanks!

Exactly what I need

Kenln · Feb 22, 2007

Is there a way to do a Pass-Through SQL Statement in VBA code to build a recordset?

namliam · Feb 22, 2007

Yes you can make the pass-through query in VBA only... but that is doing it the hard way...

The easy way is to make a Pass-through just like a normal query and open that as a recordset...

Kenln · Feb 22, 2007

Yeah you I was wondering about that as soon as I clicked 'Post Reply'.

I have gotten used to using VBA and having as few (if any at all) actual queries. It tends to give me better and of course more flexible control at the cost of complexity.

Whichever works for the needs of the app I guess.

namliam · Feb 22, 2007

Offcourse if you need it... / want it... it is doable
First off you need to define your connection, below a sample I use to connect to an Oracle database

Code:

' Global variables
Private wrkODBC         As dao.Workspace
Private con4T           As dao.Connection

' Inside a sub
    Set wrkODBC = CreateWorkspace("myODBC", User4T, PW4T, dbUseODBC)
    Set con = Nothing
    Set con = wrkODBC.OpenConnection("Connection1", , , _
        "ODBC;DSN=" & DSN & _
            ";UID=" & User & _
            ";PWD=" & PW & _
            ";SERVER=" & Server & _
            ";")
    con.QueryTimeout = 180

Then I build my sql in a variable in this case called mySQL

Code:

    Dim rst As dao.Recordset
    Set rst = con4T.OpenRecordset(mySQL)

And execute the query to a recordset so I can work with it.

I hope that helps you along....

Greets

Edit: Why have as few queries as possible?? They are there for a reason or are simply not there I hope you are not forcing yourself to not using queries, cause they can be incredably powerfull if only for maintenance...
i.e.
The same app where I got this sample I have this code triggering queries on a huge oracle system. I have the SQL in "template" ODBC queries. This has a couple of advanges...
1) It is readable to NON Access/VBA folks
2) Any change in the SQL is easy to do
3) Using the Replace function to can put values into the statement without having to do -difficult- concatination statements.

Bodisathva · Feb 22, 2007

I actually prefer building them in VBA to the query painter...but then I also consider the query painter something akin to the spawn of Satan

anyway, the below should point you in the right direction:

Code:

    Dim conn As New ADODB.Connection
    Dim rec As ADODB.Recordset
    Dim rawData As DAO.Recordset
    Dim userName As String
    
    strSQL = [COLOR="Green"]'Populate with same query you would run on the server[/COLOR]
    conn.ConnectionString = "OraOLEDB.Oracle;UID=;pwd=;Data Source=YOURDSNNAME;"
    [COLOR="green"]'you can insert the UID & PWD or prompt, up to you
    'Oracle would be the ODBC driver used here[/COLOR] 
    conn.CommandTimeout = 1800 [COLOR="Green"]'number of seconds to wait for 
                                'the command to finish[/COLOR]
    conn.Open
    Set rec = conn.Execute(strSQL)

Looks like the mailman beat me to it...oh well, consider it a second approach, but basically the same thing.

Kenln · Feb 22, 2007

Thanks I will try both ADO and DOA to be sure I can get both to work.

Because this info is on an AS-400 with, in some cases, rather large tables even with heavy server loading I have a feeling this will be faster than pulling in the entire table through a linked table and subsequent query.

I appreciate your help,

namliam · Feb 22, 2007

Looks like you are using ADO, which I consider the devils spawn

as well as using a 30 minute time out !!!! What kind of oracle system is that requiring a 30 minute time out that is rediculous.... I hope you put that there to make sure it never times out....

Limiting the amount of data that needs to be pulled over the network is ALLWAYS a good idea, thus using the Pass-through is (way) superior to using the linked table.

Bodisathva · Feb 22, 2007

namliam said:
as well as using a 30 minute time out !!!! What kind of oracle system is that requiring a 30 minute time out that is rediculous.... I hope you put that there to make sure it never times out....

without getting too specific, no. I have several data loads which requre anywhere between an hour and an hour and a half to load. My failsafe point is a 10 hour timeout.

namliam · Feb 22, 2007

Bodisathva said:
between an hour and an hour and a half to load. My failsafe point is a 10 hour timeout.

With the risk of thread jacking this a little.... WOW a full hour and a half?
That is some data migrating you must be doing....

Bodisathva · Feb 22, 2007

namliam said:
With the risk of thread jacking this a little.... WOW a full hour and a half?
That is some data migrating you must be doing....

record count in this particular DB is counted in trillions and the SQL string alone is 1600 chars long. While the heavy lifting is normally done in SAS or TSA, this particular app is just some "light" reporting work with an Access front end.

namliam · Feb 22, 2007

I have some reporting stuff going on as well. But generaly what I do is Schedule the heavy stuff overnight, which prepares all kinds of data and data manipulations leaving my apps for the easy stuff, mostly data retrieval from the pre-manipulated data... leaving the main system for the normal operators during the day

.

Kenln · Feb 26, 2007

A couple of questions:

Bodisathva said:

conn.ConnectionString = "OraOLEDB.Oracle;UID=;pwd=;Data Source=YOURDSNNAME;"

Where do I determine what I should use where you used "OraOLEDB.Oracle" from. I know in your example this is for an Oracle db. My data comes from an AS-400.

namliam said:

Set wrkODBC = CreateWorkspace("myODBC", User4T, PW4T, dbUseODBC)

Pretty much the same question as above. In this example you used "myODBC". I have been reading and rereading help and to be honest... the example lacks explanation, or at least enough for me to see how I define these two portions of the syntax.

Thank you both for your help,

P.S. I used

Code:

    Conn1.ConnectionString = "Data Source='DNSName';"

and nothing else and it worked (ADO). But I concerned about what I left out.

Thanks again.

Bodisathva · Feb 26, 2007

Kenln said:
Where do I determine what I should use where you used "OraOLEDB.Oracle" from. I know in your example this is for an Oracle db. My data comes from an AS-400.

A few examples of ADO connection strings here better yet, just Google "VBA, Connection Strings"

Kenln said:
P.S. I used...and nothing else and it worked (ADO). But I concerned about what I left out.

In my example:

OraOLEDB.Oracle = the Oracle Driver I want the connection to use
UID= The user ID to be used (if there is no username and password required, then this is obviously not needed)
pwd= The appropriate password for the User ID
Data Source= the name of the machine DSN which represents the DB connection you are attempting to access

KeithG · Feb 26, 2007

What ODBC driver are you useing to connect to AS400? Below is the connection string I use with the IBM Iseries Access ODBC Driver

Driver={iSeries Access ODBC Driver};System=my_system_name;Uid=myUsername;Pwd=myPassword;

Kenln · Feb 26, 2007

Cool, we are using the iSeries also.

So would it be:

Code:

conn.ConnectionString = "Driver=iSeries Access ODBC Driver;Uid=MyUsername;Pwd=MyPW;
;Data Source=DNSName;"

Do I need the System Name or IP Address? This is defined in the DNS Name isn't it?

namliam · Feb 26, 2007

CreateWorkspace("myODBC", User4T, PW4T, dbUseODBC)

"myODBC" = Just any old name
User = User for login into the database
PW = password

I dont know if it is needed to use that for the workspace, I did and it worked

I just used the same User/PW as I did for my real ODBC connection.

ODBC Too many indexed (1 Viewer)

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread