MS Access Query not working anymore.. help

[pka4916]

I was using Access 2003 for years, and working with no problems.
Now the company switched to Access 2007 and I have problems.



I have this code, and it just won't work anymore

Me.frmsearchresults.Form.RecordSource = "Select * from mydata"

When I use this in my VBA, The results are 0, but when I put it straight in the properties, then it's working.

When I go in Debug mode, and change it, then the results are 0 again instead of 17000 records

but when I wait like 10 seconds, then the count = 17000 all the sudden.

I don't get it, since it was working just fine in 2003.
Spent now 2 weeks on this problem, and I can't figure it out..

Anyone had any suggestions?

[Toyman]

Have you checked to ensure the db is in a folder defined in your Access 2007 as a trusted folder?

[pka4916]

Yes I did.

[Toyman]

Yes I did.

I noticed that the main form's name refers to a "searchform"; is there where clauses in the sql statement?

[pka4916]

as a matter of fact yes, but I did remove that, so the only
thing I have left is "Select * from mydata"
Since I was so confused.

[Toyman]

as a matter of fact yes, but I did remove that, so the only
thing I have left is "Select * from mydata"
Since I was so confused.

I don't know if this will work, but try this. I am assuming you are running this code via a button.

Pass the sql statement to a veriable and then assign it to the recordsource

Dim Mysql as string
Mysql = "Select * from mydata"
Me.frmsearchresults.Form.RecordSource = Mysql
Me.frmsearchresults.Form.Requery

[pka4916]

That's correct. I am running it through a button.
I tried that as well, and did not make any difference.

Btw. I did try it with a different Table, and then it works for some reason.
But if I use my other table, then it refuses to work..

I deleted the table, and created a new one, with just 3 fields, and still the same issue.

It's just a plain Text field, a Date field and an Autonumber.

[goodguy]

Try adding an index to the mydata table.

[pka4916]

I added an index for each one of them, and also just 1 by one.
Did not make any difference.

[goodguy]

Maybe your mysql server is slowing the connection? Is it on a network?

[pka4916]

I'm Not using an SQL server. It's just a MDB file linked to another Mdb file, with linked tables.
both local on the PC. Never was a problem before with 2003, now with 2007 it's not working

[Toyman]

I'm Not using an SQL server. It's just a MDB file linked to another Mdb file, with linked tables.
both local on the PC. Never was a problem before with 2003, now with 2007 it's not working

Copy the actual table onto the front end and try to see if that works. Is the backend created from a different version of Access than your front end? Also, I noticed that you are running a 64 bit Access 2007. If the version in which the original mdb was created was created with a 32 bit, they are not compatible

[pka4916]

I'm running 64 Bit at home, but 32 Bits at work.. sorry.

So it is in this case 32 bits.
The file is indeed created in a different Access..
Could that be the problem?. Maybe doing a compact/repair will do it?
Just guessing here... Can't do anything until I am back at work Monday.

[Toyman]

I'm running 64 Bit at home, but 32 Bits at work.. sorry.

So it is in this case 32 bits.
The file is indeed created in a different Access..
Could that be the problem?. Maybe doing a compact/repair will do it?
Just guessing here... Can't do anything until I am back at work Monday.

The capability issue is most likely the cause. You cannot fix it by compact/repair. In reading the issue on the web, I came across several artical dealing with running 64 bit in 32 bit invironment. Here is one.
http://msdn.microsoft.com/en-us/libr...using32vs64ace

[pka4916]

Tried everything, that I can think of, it's just not working..

I am at a loss here