Using Access 2007 with AS400 backend

[FishVal]

Hi there FishVal

Thank you for your input. Well, I am actually trying that at the moment(creating my own record navigation buttons etc.) , buy use of recordsets, but I am having all sorts of problems with it. As I am new to VBA, it's a bit of a minefield. Even writing SQL statements is not straight forward and I am getting all sorts of syntax errors.

I dont want to just give up......

Maybe this would be helpful. If you have one master table you can link it to Access form linking its fields as well and put additional unbound controls updating the rest tables programmaticall y. This way you will get, at least, low cost Access navigation bar. Not less than half the job. ;)

[bloukopkoggelmander]

Fishval

Thanks for that Buddy. Well, at the moment my form is made up of unbound controls and then behind it, I have a load of VBA and SQL to carry out all the tasks. So I think I have it at the moment as you describe, but being a bit of a dummy me it is a bit hard going. My main falldown at the moment is to get the syntax correct for very large SQL SELECT statement, selecting records from about 5 differnet tables to load into a recordset.

[NeoPa]

Hi NeoPa

Right , the only thing I can find out about the AS400 OS is V5 Release 3. I know that propably does not help much. I have also been told I will not be allowed to put any additional software on the server. So door closed there.....

That sounds like IBM AIX v5 R3 to me. Very similar to what we have here. I was really asking about the software which provides the ODBC link. It's not important. It's only if it were EasySoft ODBC that I would have any understanding of it anyway, and I don't even administer that. I just link to it.

However, I know the tests indicate that it can't be this software as simple table updates still work, but I still suspect it is (It's possible for the differences to be more subtle than your tests allow for). I suggest you start up a dialogue with the people who support that software. They may be able to help you with debugging the problem.
Good luck.

[bloukopkoggelmander]

Hi Neopa

They had a look but keeps telling me it's a problem with Access and not the AS400 .So it looks like I am pretty much much on my own on this one :-(

For my ODBC linking I am using IBM Iseries Access for Windows. Within that there is a tool ODBC Administration I sue for setting up the links.

[FishVal]

Fishval

Thanks for that Buddy. Well, at the moment my form is made up of unbound controls and then behind it, I have a load of VBA and SQL to carry out all the tasks. So I think I have it at the moment as you describe, but being a bit of a dummy me it is a bit hard going. My main falldown at the moment is to get the syntax correct for very large SQL SELECT statement, selecting records from about 5 differnet tables to load into a recordset.

Would you be so kind to post tables metadata. I'll be able to give you more definite advices.

[bloukopkoggelmander]

It is going to be a lot of code.... So not just the SQL statement I am working on, but all of it?

[FishVal]

It is going to be a lot of code.... So not just the SQL statement I am working on, but all of it?

I've meant tables PK/FK relationships only.

[bloukopkoggelmander]

Oh sorry man. Well, that's a bit tricky because you cannot set foreign keys on a AS400, only Primary keys. (You cannot set autonumbers either for that matter)

But what I have done is to set a primary key on every table( I have a bout 35 tables). I then created two fields in every table exactly the same( hoping in some way it can act as foreign keys). These two are 1) Vehicle registration number and 2) Vehicle Invoice number. The properties for each of these fields have been set exactly the same on every table.

I know the above sounds a bit Andy Pandy, and I have thought that this could be a reason why Access is throwing a wobly.

This makes things a bit more difficult doesn't it?

[FishVal]

Oh sorry man. Well, that's a bit tricky because you cannot set foreign keys on a AS400, only Primary keys. (You cannot set autonumbers either for that matter)

But what I have done is to set a primary key on every table( I have a bout 35 tables). I then created two fields in every table exactly the same( hoping in some way it can act as foreign keys). These two are 1) Vehicle registration number and 2) Vehicle Invoice number. The properties for each of these fields have been set exactly the same on every table.

I know the above sounds a bit Andy Pandy, and I have thought that this could be a reason why Access is throwing a wobly.

This makes things a bit more difficult doesn't it?

Not so difficult as it seems. I'll make some tries and will be back with some kind of solution. The last thing I'd like you to straight out is whether you have master table (or maybe separate tables for each key) where primary keys are stored?

[bloukopkoggelmander]

Yip, the main table where all others ' hang off ' from is my main vehicle table which contain unique information on each vehicle. Here I have set two primary fields : 1) Vehicle Registration Number and 2)Vehicle invoice number.

The reason for this is that during the initial stages of ordering a vehicle up until delivery, the only way to identify it is by Invoice number. After delivery it can then be identified via Registration number.

So therefore I have a tabbed page. Tab one is the purchase order stage(invoice number used here.) The user fills this page in.

The next tab is delivery. The user will complete this at a much later date than tab one. From here on the vehicle registration will be known. The user completes this tab on delivery.

Third tab is for allocation where a vehicle registration will be linked with an employee number.

There are then more tabs that follows to complete a vehicle lifecycle until it is disposed. And so each tab on the one form would be completed over a long period of time.

Hope this helps.

[FishVal]

Yip, the main table where all others ' hang off ' from is my main vehicle table which contain unique information on each vehicle. Here I have set two primary fields : 1) Vehicle Registration Number and 2)Vehicle invoice number.

The reason for this is that during the initial stages of ordering a vehicle up until delivery, the only way to identify it is by Invoice number. After delivery it can then be identified via Registration number.

So therefore I have a tabbed page. Tab one is the purchase order stage(invoice number used here.) The user fills this page in.

The next tab is delivery. The user will complete this at a much later date than tab one. From here on the vehicle registration will be known. The user completes this tab on delivery.

Third tab is for allocation where a vehicle registration will be linked with an employee number.

There are then more tabs that follows to complete a vehicle lifecycle until it is disposed. And so each tab on the one form would be completed over a long period of time.

Hope this helps.

Ok. I have some ideas, will be back when check 'em.

... I am afraid the option to use a form/subform layout is not possible. This has been proposed, but turned down by the users it is being developed for.

By the way if tabbed control is ok, you can add subform to tabs in such a design that it looks like no subform at all is present ;).
Users will never find a difference but it will help to update FK fields via form/subform automation.
It may be a pretty light coded solution if each tab (or at least asome tabs) contains fields from one table only.

[bloukopkoggelmander]

Morning FishVal

Well, I have tried the subform option, but just cannot seem to make it look like a normal form. I have played around with the properties to get rid of borders and all sorts, but are still left with some remnants of the subform and it just ends up looking ugly. I must be missing something somewhere.

I will have another play this morning to see if I can edit a subform to a point where only the controls on it is visible.

[bloukopkoggelmander]

Well, I tried to hack the subform apart a bit, but just cannot get it to look like a standard from. It would only display in datasheet view, so looks more like a spreadsheet than a form with controls :-(

[nico5038]

A subform is by default made visible as a datasheet, but under the subform's Format properties tab the datasheet view is best changed to single (or continuous) form.
Thus you can position the fields as needed and even by dropping the border make the subform "invisible" for the user.

Nic;o)

[bloukopkoggelmander]

Howzit Nico

Sorry, forgot to mention that I have done that as well. However when changing it to either Single From or Continuis Form and change to normal Form view, the subform is greyed out and no controls are displayed, When changing back to Design view again, the controls are visible within the subform.

I have played around with the form and control properties, but cannot get it to show.