Search results

The actual table name in my code AND database was “tblVolumes”, I was using TableName purely as an example.

From my initial post: "After linking to a table in an external db, I am attempting to open a recordset using the simple code shown below: Code: Set rst = currentdb.openrecordset(“SELECT * FROM TableName”) When executing this statement I get the error message as shown above, even though the...

Thanks for this, had everything else failed I would have tried this. In fact, if I have time I still might as it would be interesting to see what could be causing this issue. Thankfully, I've managed to get round this (touch wood!) by actually opening the external db and opening the recordset...

Thanks but I wish it were as simple as that.

Thanks for your reply. With regards to Access limitations and SQL Server, I refer to a previous thread of mine which, as it happens, you also posted on. http://www.access-programmers.co.uk/forums/showthread.php?t=205226 Ah, that makes sense now, previously I had always been more inclined...

“Run-time error '3078': The Microsoft Jet database engine can't find the input table or query TableName” After linking to a table in an external db, I am attempting to open a recordset using the simple code shown below: Set rst = currentdb.openrecordset(“SELECT * FROM TableName”) When...

Thanks, that didn’t really solve the problem but that’s a useful piece of information. However, intuitively I would have thought that using YEAR - as opposed to DATESERIAL - would be more efficient. What do you mean by function call though as I didn’t really understand your explanation?

Hi All, I have a problem linking to tables in an external Access database. I have to run through a loop potentially 1000’s of times, and each time my code needs to link to an external table (depending on certain criteria), extract the data and manipulate it. Then my loop deletes the link and...

That is very true, the amount of “waste” I have seen (not just with our IT policies) due to extra bureaucracy and unnecessary measures in place, is staggering. I suppose in a very large organisation there is a need to have consistency and rules in place vis-à-vis anything IT related, as not all...

It’s similar but worse than what Banana mentioned. You’d actually have to see it to believe it - it’s an absolute nightmare. As I work in a very large company, the bureaucracy involved in submitting a request and justifying a business case (in the cost cutting environment we’re in) has turned...

Essentially, I’m only really using the secondary database for doing calculations, creating, using and deleting temporary tables. I need to do the same series/steps of calculations 1000’s of times, and if Access didn’t have a 2gb limit, I could probably do it all in one database. So far, with...

Thanks for your reply. Unfortunately, this is at work and I'm restricted in that I have to use Access. However, they are working on getting SQL Server, but in the mean time I'm pretty much stuck in this respect....

Hi All, As I am manipulating huge volumes of data using VBA and SQL, the Access database I’m using (the master database) becomes prone to exceeding it’s 2gb limit. In order to solve this problem, I am trying to do most of the manipulations/calculations in an external (secondary) database, and...

Yes, you are correct. After much searching it does seem like the conditional join functionality is not available in Jet SQL so your suggestion is the best course of action. Thanks

Hi All, I am trying to run a query that joins 2 tables, however, the joins will be different depending on certain criteria. I have 2 tables as follows: TblMonth Month MonthlyTotal Dec-08 Jan-09 Feb-09 Mar-09 etc TblSeason Season SeasonalValue Winter09 200 Summer09 100 Winter10 212 Summer10...

Works a charm, thanks

Thanks for this, it almost works perfectly. My only problem now is the ordering of the original column names down the rows. I'm ending up with a table containing the following column: ProfRevCost Cost Profit Revenue Cost Profit Revenue etc It's obviously ordering it alphabetically down the...

Hi, Is it possible to use the UNPIVOT function in Access SQL? I have a table with 5 columns (Type, Category, Profit, Revenue, Cost) as shown below: Type Category Profit Revenue Cost A Small £150 £200 £50 A Medium etc A Large B Small B Medium B Large C Small Etc What I want to be able to...

Well, ideally, they would both be run over a network but, failing this, they will have to be both run on the C drive. Currently, for testing they are run on the C drive. Essentially, the issue is occurring locally after running the SQL query. For some inexplicable reason, on my work PC the...

Both the master and secondary databases can be on the C drive to run, the network is only used when running the initial SQL query to extract information from the linked Oracle tables.