Replace OpenDatabase with?

I have a MDB that consists largely of a number of linked tables to
external data files, which contain various activity data. I then open
this db from my ADP project using OpenDatabase and OpenRecordset,
which I believe are based on DAO (are they?).
I am in the process of attempting to remove all DAO code from the ADP.
I have never used ADODB to open an MDB, however, and I was wondering
if anyone has a few pointers? It's pretty simple code...
'open the file via the linked table in the mdb, and check the date
inside it
Set db = OpenDatabase("L:\Tools\Reconciliation
\Reconciliation.mdb")
Set act = db.OpenRecordset("Activity")
startDate = CDate(act.fields(1))
I'm hoping this can be converted to something using ADOBD and
connection strings. I'm pretty familiar with these, but they seem to
be extremely fragile so any advice to start would be great!
MauryDim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Do some stuff here
cnn.Close
Set cnn = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
>I have a MDB that consists largely of a number of linked tables to
> external data files, which contain various activity data. I then open
> this db from my ADP project using OpenDatabase and OpenRecordset,
> which I believe are based on DAO (are they?).
> I am in the process of attempting to remove all DAO code from the ADP.
> I have never used ADODB to open an MDB, however, and I was wondering
> if anyone has a few pointers? It's pretty simple code...
> 'open the file via the linked table in the mdb, and check the date
> inside it
> Set db = OpenDatabase("L:\Tools\Reconciliation
> \Reconciliation.mdb")
> Set act = db.OpenRecordset("Activity")
> startDate = CDate(act.fields(1))
> I'm hoping this can be converted to something using ADOBD and
> connection strings. I'm pretty familiar with these, but they seem to
> be extremely fragile so any advice to start would be great!
> Maury|||On Mar 25, 10:27=A0pm, "Dave Patrick" <DSPatr...@.nospam.gmail.com>
wrote:
> =A0 =A0Dim cnn As ADODB.Connection
> =A0 =A0Set cnn =3D CurrentProject.Connection
> =A0 =A0'Do some stuff here
> =A0 =A0cnn.Close
> =A0 =A0Set cnn =3D Nothing
Ummm, no.
Maury|||That's very descriptive!
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
Ummm, no.
Maury

Replace OpenDatabase with?

I have a MDB that consists largely of a number of linked tables to
external data files, which contain various activity data. I then open
this db from my ADP project using OpenDatabase and OpenRecordset,
which I believe are based on DAO (are they?).
I am in the process of attempting to remove all DAO code from the ADP.
I have never used ADODB to open an MDB, however, and I was wondering
if anyone has a few pointers? It's pretty simple code...
'open the file via the linked table in the mdb, and check the date
inside it
Set db = OpenDatabase("L:\Tools\Reconciliation
\Reconciliation.mdb")
Set act = db.OpenRecordset("Activity")
startDate = CDate(act.fields(1))
I'm hoping this can be converted to something using ADOBD and
connection strings. I'm pretty familiar with these, but they seem to
be extremely fragile so any advice to start would be great!
Maury
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Do some stuff here
cnn.Close
Set cnn = Nothing

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
>I have a MDB that consists largely of a number of linked tables to
> external data files, which contain various activity data. I then open
> this db from my ADP project using OpenDatabase and OpenRecordset,
> which I believe are based on DAO (are they?).
> I am in the process of attempting to remove all DAO code from the ADP.
> I have never used ADODB to open an MDB, however, and I was wondering
> if anyone has a few pointers? It's pretty simple code...
> 'open the file via the linked table in the mdb, and check the date
> inside it
> Set db = OpenDatabase("L:\Tools\Reconciliation
> \Reconciliation.mdb")
> Set act = db.OpenRecordset("Activity")
> startDate = CDate(act.fields(1))
> I'm hoping this can be converted to something using ADOBD and
> connection strings. I'm pretty familiar with these, but they seem to
> be extremely fragile so any advice to start would be great!
> Maury
|||On Mar 25, 10:27Xpm, "Dave Patrick" <DSPatr...@.nospam.gmail.com>
wrote:
> X XDim cnn As ADODB.Connection
> X XSet cnn = CurrentProject.Connection
> X X'Do some stuff here
> X Xcnn.Close
> X XSet cnn = Nothing
Ummm, no.
Maury
|||That's very descriptive!

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
Ummm, no.
Maury

Repeatedly starting databases

Hi all,

I've been handed a SQL Server that is used as an MIS source. There are 4 databases that carry out the task of importing data from various sources, then manipulting that data, and offering the data for reporting purposes.

The vendor has also created several other databases (of which there are also 4), but no-one in my company seems to know the purpose of these dbs.

In the logs, there are approximately 8/9 messages per second - not every second, but numberous seconds per minute - stating...

Starting up database 'db_name'.

... each time, all 4 of the mysterious dbs appear.

I've checked the spid that is running this job this morning, and it seems to be NT AUTHORITY\SYSTEM connected to one of the original 4 report databases.

Does this have any affect on the performance of the server, or the specific db attached to the user?

Thanks in advance.

Duncancheck the AUTO CLOSE option, i bet it is checked.|||Hi Duncan,

The first thing I would do is get in touch with the vendor and find out what the purpose of these databases is?

Cheers|||Thanks guys

Yes auto close is checked.

And yes, I'll contact the vendor to find out why they've done this.

Before I do, could there be any benefit to this since the databases are used at least once a second (or maybe two seconds at most) throughout the day?|||Originally posted by zinedunc
Thanks guys

Yes auto close is checked.

And yes, I'll contact the vendor to find out why they've done this.

Before I do, could there be any benefit to this since the databases are used at least once a second (or maybe two seconds at most) throughout the day?

What this option does is closes the database connection after the last user disconnects from the database. when a user tries to acces the databases again and requests for the same, then the database has to be reopened, which takes time and overhead.
Since your database is accessed frequentl, it has to be closed and reopened frequently which puts a large performance on the server and the users or the application which is using it.|||Thanks harshal_in

I knew that that's what was happening, I just wanted a second opinion before I go banging some heads :-)

Repair SQL database that has been marked suspect

I woke up this morning to find my company's SQL database inaccessible and marked suspect.

I have used various t-sql commands to bring it into Emergency mode.

Now it is labelled Loading\Offline\Read-Only\Emergency Mode.

I'm using SQL Server 2000

I do not have a backup and I need to get the database up and running as soon as yesterday.

Help

first of all what version of SQL Server are u using?

Second ..There can be many reason for database being suspect.Please see in to the SQL Server error log and send if you see something unusual/error .It is very importent to look in to error log before anything can be done

run this on master

select status from sysdatabases and send it.

Thanks

|||

Recommended actions for corrupt or suspect databases

http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

AMB

|||

it is very tough to get a suspect db back. try this link http://www.myitforum.com/articles/18/view.asp?id=7381

Madhu

Reordering SQL Tables

Hi,

I have inherited an inhouse employee self service application used to display various employee data.

I have one table that needs to have the data permanently reordered, as the front end web page reads the data from the table from the top to the bottom, but somehow this data has got mixed up and is not in the sequential order that it should be in.

I am new to SQL and haven't a clue how to change the web code, so was hoping I could run a script on that table to reorder the data in an ascending manner on one of the columns.

Is this possible? How can I do this?

Thanks

Chris

Hi Chris,

When you say "not in the sequential order" do you mean there is a column that has incrementing numeric values such as 1,2,3...100000 or that you expect results to be ordered by something like customer name etc?

You can only "order" a table via a clustered index, but this has no impact upon client query result formats (in fact it did pre 2000 if no order by was specified, but that's another story). You will need to specify an ORDER BY in the client, or in a view (via the TOP clause) or stored procedure. You cannot rely upon sql server to return ordered results unless you have explicity stated what and how you wish the ordering to ocurr.

Cheers,

Rob

Rendering report when one dataset times out

We use Report Server reports to provide our support staff with views into several production databases on various servers. The most commonly used report is a summary of the data on several production servers. The report contains multiple datasets. Each dataset points at a different server. If one of the servers is down some reason, one dataset fails to execute, and the entire report fails to to render.

What I would like is some control over this behaviour. If a given data set fails to execute, I would like the report to continue and render the results. The failed data set would obviously show no data but this is fine by me.

I know I can place timeout values on each dataset. But this doesn't help me.

Can I structure my report or SQL in such a way that the report survives the failure of a dataset to execute because a remote server is down?

Thanks in advance for your help

Hi,

no actually not. YOu have to put the query within a stored procedure, catching it up with exception handling (Hope you are on SQL 2k5) or create a own data extension with .NET to provide a dataset to the report. In the custom coded data extension you will have all the functionalities of the .NET Framework to catch those errors.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||You may use link server to collect the data on others DataBase, And put them in Temp DB...