Not really sure what to ask

[sdanks]

I've been searching for "ODBC" on the boards, and I still dont know how it works with Homeseer. Can I setup an ODBC connection to mySql and then put data in the database or change the table structure, etc. from a script in Homeseer? I cant find any help files that even mention ODBC. If this can be done, what syntax is used?

[Jim Doolittle]

Yes ODBC can be used via scripting. Try searching again looking for ADO (here is results of one search and you might try a groups.google.com search of ADO and VBScript.

Using ADO, you can create tables and add entries within your own scripts.

BTW, take a look at the UltraLog Plug-In. With it you can specify the MySQL as the database engine and populate tables with HomeSeer log that you can use with your own data tables.

[Rupp]

sdanks,
ADO and a DNSless connection are a much easier way to access a DB.

Here's an excellent site with excellent examples.

http://www.w3schools.com/ado/ado_connect.asp

[Rick Tinker]

Although I am quite disappointed to learn that under ADO.NET, there is no way to create a database. To do that, you use ADOX, and that means you are using a COM interface.... Yuuuuuuckkkkk!!!

[Rupp]

Rick,
It appears they want you to use MSDE as the database of choice for development. No?

[Rick Tinker]

MSDE - the acronym sounds familiar but I can't quite place it. If this is the built-in visual database designer, then no because that helps you create databases that you deliver with your application. What I am talking about is the programmatic creation of the database. ADO.NET will allow you to create tables and such, but will not let you create the database itself. It is a big (IMHO stupid) inconsistency - I want to get rid of COM altogether and yet they do not provide the tools to do so.

[Rupp]

Rick,
MSDE is the free SQL lite database that Microsoft is offering to be used as the development database of choice.
http://www.microsoft.com/sql/msde/downloads/default.asp


They also offer a web interface that allows the creation and editing of tables and data:
http://www.microsoft.com/downloads/d...displaylang=en

[Rick Tinker]

Oooooh - I read something about that but failed to see that it was *FREE*.

Perhaps it is time to consider an alternative to MS Access. However, and let's assume that I have more database creation tools with this, which would users find easier to work with - MS Access or the SQL-Lite product?

[Rupp]

Rick,
In our dot net class 2 weeks ago we used the interface and it was more intuitive than the Access interface. The hardest part was configuring access permissions.

[Rick Tinker]

So skip ahead to where they discuss programmatically creating the database, tables, and rows in the tables and post a URL to the appropriate MSDN page for me to save me from having to Google it.

[Jim Doolittle]

I assume you want to stay away from ADOX so that you do not have to worry about users have MDAC and JET installed. Do you plan on the user of your applications needing to create a database or just use a database.

If the latter, then write a separate app that uses ADOX on your development machine to create the database and provide an empty one in the application package.

I read a good web article comparing MSAccess, SQL Server, and MSDE but didn't save the link. I believe that MSAccess was still recommended for personal use and that SQL Server was more intended for high traffic commercial use.

MSDE is a development environment and should only be used for building applications. It is to SQL Server what Personal Web Server/IIS5 w/ XP are to the full blow IIS. In other words, it a version that is "crippled" with respect to number of connections allowed.

The more I read the more it looked like you should stay away from MSDE but looking back I cannot recall the arguments. My argument in favor of it is that it is free and MSAccess and SQL Server are not.

Since, I have MSAccess and have had it for quite some time, I use it for my databases. I have played with MySQL and I would almost consider it over MSDE and SQL Server, again because it is free.

[Rupp]

Jim,
Just one mans opinion but our instructor stated that the MSDE is a not a crippled version of SQL server but simply comes with no user interface and a 25 concurrent user limit. He stated that all the projects they were developing in his real job are written on top of MSDE and if the end user needed to move to a full blown SQL Server Database then they simply change the name of the database the app is pointing to.

[sdanks]

My biggest concern on using MsAccess is that up at work, we are constantly having to pack the database and run the repair tools on it. We use it for call tracking for our helpdesk. every few weeks it needs fixing. (Access 97)

[Jim Doolittle]

<BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by Rupp:

... MSDE is a not a crippled version of SQL server but simply comes with no user interface and a 25 concurrent user limit. <HR></BLOCKQUOTE>

I consider a 25 concurrent user limit as a "crippled version" of SQL Server. However, that limit should be fine for most user applications.

Similar with IIS with Windows XP Pro which I believe is crippled with a 10 concurrent user limit. There are hacks reported that try to remove this limit and some have bumped the limit to 50.

<BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by sdanks:
My biggest concern on using MsAccess is that up at work ... <HR></BLOCKQUOTE>

I think that "at work" can be considered as commercial use in which case I would not recommend MSAccess.

[Rick Tinker]

Jim,

I think you have been a little misinformed...

MS Access (the GUI app) is not free, but the runtime necessary to access access (as it were) are free. IOW, I can have the application use an MS Access database and deliver the Access runtime without you having a copy of MS Access.

Also, MSDE is not a development environment - it is a trimmed down version of MS SQL.

I want to avoid ADOX for the exact reason mentioned - ADOX means an ActiveX (COM) requirement for HomeSeer which is to be avoided at all possible costs.

The reason for wanting to create the database programmatically is so that when you do a "Save As..." in HomeSeer, it can create a new database. I am thinking now that having an empty database template that I can copy may be better just so I can get rid of ADOX - Rich would like nothing more than for me to get rid of ADOX.

Now that Rupp (thank you sir) has pointed me at MSDE (which I honestly never heard of before) I have successfully programmatically created a database in HS 2.0. So, the question becomes, is there any benefit of one over the other if I go ahead and decide to create the MS Access database using a template file instead of ADOX?

As for database maintenance that MS Access would require, it is not difficult to do a "Compact & Repair" programmatically so that the deleted database items are cleaned up and the database size adjusted.