| Author |
|
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 05 2018 at 15:53 | IP Logged
|
|
|
I have been unable after hours of trying to connect
either Access or SQL Anywhere to the pwrhome.db file.
(Yes it was a copy of the ph file so it was NOT locked
against access.)
Nothing seems to understand what a ".db" file is.
Actually I did manage to get Access to open the file
but then the save created a ".odb" file which PH
couldn't read!!!
:-(
Can anyone post the details of how to connect?
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 05 2018 at 20:03 | IP Logged
|
|
|
Ken,
Take a look at this forum post: http://www.power-
home.com/forum/forum_posts.asp?TID=1583&KW=access+ph+odbc
It's a little dated so the SQL Anywhere Driver is now version 11. You don't want to use Access to connect directly to the pwrhome.db file. It would
have no idea how to interpret that. You need to go into ODBC administrator within Control Panel and create a new entry that points to the pwrhome.db
file using the SQL Anywhere 11 driver. You should also be able to use a userid of PH and a password of PH and then test the connection.
With that done, from Access, you should be able to connect to external ODBC database and specify the entry you just created.
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 06 2018 at 11:46 | IP Logged
|
|
|
Dave -
Many thanks. I hate to admit it BUT I am stumped. I
have attempted for over 2 hours to get into the PH
database without any success.
I installed SQL Central 17.0 but no matter what I try
I am unable to access the ph data. I have tried
multiple different configuration approaches w/o
success.
The referenced old Forum posting was of no help. Some
of the links are no longer valid and the instructions
don't match up with anything at my end.
Could you post some specific "how-to's" ?
Probably the easiest way is to indicate what typical
answers would be to the "ODBC Configuration for SQL
Anywhere" setup window "Login" tab (and any other tabs
if they are significant.
This is what I tried (from my desktop computer accessing the unopen "prwhome-now.db" backup file on the PH computer) using the Control Panel... Administrative Tools>ODBC Data Sources (32-bit)...
The problems I am seeing start with the right answers
to the various Action, Host, Port, Server Name,
Database Name questions. Nothing I have tried even
pings much less connects. :-(
Thanks...
ken
Edited by GadgetGuy - December 06 2018 at 13:16
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 06 2018 at 19:00 | IP Logged
|
|
|
Ken,
Hopefully text and screenshots that follow will make it clear.
Im currently running Windows 10 on the machine that I did these screenshots from. I am also using Access 2010. Different versions may have slightly different
screens but you should get the idea. One other thing I forgot to mention is that Im running Classic Shell so that my Start button behaves like a Windows 7
Start button.
The first thing I did was click on the Start button and in the "Search programs and files" I typed "ODBC" and hit enter. From the list that comes up, I chose
"ODBC Data Sources (32bit)" at the top under "Programs".
After launching the ODBC administrator, this is what it looks like:
On the System DSN tab, press "Add". In the resulting "Create New Data Source" window, select "SQL Anywhere 11" and press "Finish":
In the "ODBC Configuration for SQL Anywhere 11" window, fill out the ODBC, Login, and Database tabs as detailed in the following 3 pictures. The userid is ph
and the password is ph. The Network and Advanced tabs can be left at their default:
When done configuring the 3 tabs, press OK and it should look like this:
When that is complete, your ODBC DSN for PowerHome should be configured and is now ready to be utilized in any ODBC compatible program (Access, Excel, etc).
Launch Access. Create a new blank database:
With your new blank database opened, click on the "External Data" menu and choose "Link to the data source by creating a linked table.":
Select your newly created ODBC entry for PowerHome:
Select the PowerHome table(s) that you wish to link to Access:
Select the primary keys for each of the tables you selected. Typically this will always be at least the first column:
After you've selected your linked tables, you can then right click and Open them to view and manipulate the data:
Hope this helps.
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 07 2018 at 10:58 | IP Logged
|
|
|
Dave -
Thanks !! That was a very useful tutorial, which I'm sure will help others, as this seems to be a moderately asked question and this fresh insight is great.
Finally opened the two Tables.
They are BOTH totally empty, yet I'm still getting the IR Error on Startup.
Any more ideas?
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 07 2018 at 19:35 | IP Logged
|
|
|
Ken,
I took a look through the code and that error message is only produced by an Infrared capable controller or by making a call to an infrared command (ph_ir
function or similar or an IR call from a macro). Nothing in normal PowerHome startup would be attempting to read from the IR table. You would not get this
error as a result of database corruption.
The error is indicative of trying to read data from the IR table. I would check your startup trigger to see what is being called. I would also do a
database search for %ph_ir% to try and locate where you may be calling an IR function.
If you cant find it, zip a copy of your database for me and I'll take a look.
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 07 2018 at 20:20 | IP Logged
|
|
|
Dave/Peter -
You are soooo smart it scares me. 
You were both right-on to a piece of the puzzle and combined you got it!
Look at what I found as the last entry in the PH Initialize macro...
That was it and everything works OK now! This error has been bugging me for over a year. What a relief to finally get rid of it.
Many THANKS.
Edited by GadgetGuy - December 07 2018 at 20:23
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 08 2018 at 06:57 | IP Logged
|
|
|
A closing question on the database discussion.
After successfully using Access to look at the
pwrhome.db file, I noticed that upon closing Access
the file was saved as "pwrhome.accdb"
Does this indicate that Access can only be used to
"Look At" things but not make changes?
If "changes" are possible, then how does one get back
to a database (eg, .db) format that PowerHome can use?
I saw no such Save options to create a ".db" file.
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 08 2018 at 18:14 | IP Logged
|
|
|
Ken,
Access always creates its a database table (the blank database option you chose at the beginning). This is where all setup is stored. If you chose the "Link to
Data Source..." vs the "Import the Data Source..." then what you have are "linked" tables. The data isnt actually in the .accdb file...just the info to link to
the actual PowerHome database table(s). If you are linked, then any changes made from Access are actually made in the PowerHome database. Access doesnt know how
to talk to a Sybase SQL Anywhere table and cant save in that format. All it really understands is its own database format AND ODBC data sources. ODBC is a
translation layer that takes standard ODBC commands and translates them to SQL Anywhere commands because that is the driver you chose. Most database formats will
include an ODBC driver so you can take advantage of any tool that can speak ODBC.
If you imported the data, then the data is copied to equivalent tables in your Access .accdb file. Changes you make in this instance will only be to the Access
copy of the PowerHome data. You can save this data back to a SQL Anywhere database file.
If you want to select/update data in the pwrhome.db database file from Access, just make sure you're "Linking". Then you are viewing data in the pwrhome.db file
as well as updating in that file if you make changes. The .accdb file is just an Access file to store the ODBC and table link information.
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 08 2018 at 20:11 | IP Logged
|
|
|
Ahh, Dave Thanks.
In retrospect that should have been obvious to me
after going thru the linking process to establish the
connection. I guess I just didn't expect Access to
also save a copy.
I just gave myself a little kick in the butt for being
so blind.
Thanks again for all your assistance on this. It is
so very nice to not get bugged with an IR error
comment every time I restart, which has been often
recently as I am making some home control changes.
In addition, learning how to use Access will now be a
lot easier than SQL which I don't use enough anymore
to be fluent.
-ken
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
renard
Groupie
Joined: November 01 2009
Location: United States
Online Status: Offline
Posts: 74
|
| Posted: June 01 2023 at 01:41 | IP Logged
|
|
|
Dave,
Can you update these instructions to create an odbc connector for the powerhome database. These instructions reference SQL Anywhere 11 and the earliest odbc package I can find on the SAP SQL Anywhere Database Client Download page is for Anywhere 12. I am not able to get a connection for Access using the Anywhere 12 odbc driver. There is also language about licensing the odbc driver. What does that mean? BTW, should I be using the 32bit or 64bit driver? Also, the Start Program on the driver configuration/login page is completely different from what you have here.
Thanks,
Terry
__________________
Terry
|
| Back to Top |
|
| |
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome General
Topic: How Connect SQL to PH db File?
