ado connection to access vs. sql server?

[Loane Sharp]

Hi there

I need to perform quite tricky calculations (too tricky to be done using SQL
statements in a straightforward way) on fairly large datasets (too large to
be kept practically in Excel workbooks).

I keep the data in Access and retrieve the necessary data piecemeal using an
ADO connection from Excel.

I've reached the 2GB size limit on an Access database, so I'm keeping the
data in multiple files, which needs quite careful tracking, several ADO
connections and many recordsets.

I'm thinking of upgrading to SQL Server, which doesn't have the 2GB limit.
Is there likely to be a performance improvement, other things equal of
course?

Best regards
Loane

 

[Loane Sharp]

Is there likely to be a performance improvement, other things equal of
course?

-- I mean, speed? The problem the way I'm doing things is that it's so slow
....

 

[Bob Phillips]

There should be a speed improvement, more functionality, and greater
security and resilence.

Of course, your databse design will be important too.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Loane Sharp]

Thanks RP
It's time to bite the bullet I think ... Doubtless my database design has
flaws, but tweaking this has produced very limited incremental returns.
Cheers
Loane

 

[Bob Phillips]

Loane,

If you have access to SQL Server, whilst there is an inevitable learning
curve, I think you will find it a much more rewarding development
environment than Access, and that's before you even start on the production
benefits.

Good luck with it.

Bob

 

[Guest]

Hi Loane

Just curious:

I need to perform quite tricky calculations (too tricky to be done using SQL

If you are not going to use SQL to return the recordset (ADO.Net returns a
Dataset) what will you use?

I've reached the 2GB size limit on an Access database, so I'm keeping the
data in multiple files, which needs quite careful tracking, several ADO
connections and many recordsets.

You may well be ready to move up but you could have two DB's in Access,
write the initial Queries at the Access level and call them from Excel for
processing.
You should see a substantial increase in speed and performance..

Good Luck
TK

 

[Jamie Collins]

If you are not going to use SQL to return the recordset/Dataset
what will you use?

I read the OP's statement as meaning the calculations are too complex
to be completed solely using SQL's limited calculation functionality,
so they intend to bring some data into a workbook using SQL and finish
the calculations in Excel.

Jamie.

--

 

[Guest]

Hi Jamie

Semantics

I read the OP's statement as meaning the calculations are too complex
to be completed solely using SQL's limited calculation functionality,
so they intend to bring some data into a workbook using SQL and finish
the calculations in Excel.

I'm sure that is what he meant, it's a semantics thing just as I said
if you are not going to use SQL to return the recordset (ADO.Net
returns a Dataset) what will you use?
and you quoted me:
I was just pointing to some alternatives, if I was making the
decision it would depend on:
1 cost
2 are we going to make changes and then outgrow
the system again if so how soon what
3 do we have the skills in house if not how much
to do the job
We haven't even considered the report generator,
Access has an excellent generator. I think most
server people still use Crystal
Forget VB6 report generator you have to use it
with the DataEenvironment and it is next to worthless
and not even supported in VB.net

I for one don't think the 2gb limit is the only issue.

Good Luck
TK