Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.

Dave

-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Thursday, June 07, 2012 6:35 AM To: support@drupal.org Subject: Re: [support] Create and manage content directly from the database.

Standard stuff, see http://drupal.org/documentation for a good bit of information.

You just need to create a hook_menu implementation that gives a page callback to a custom form displays the data and whose submit action modifies the data.

Earnie.

On Thu, Jun 7, 2012 at 4:46 AM, Wipe_Out wipe_out@users.sourceforge.net wrote:

Hi,

I am creating a booking management application and the bookings are

largely

generated and managed from a MS Access front end working directly on a

MySQL

database.. Now we need to integrate these bookings into the Drupal

website

so that users can add or update bookings for themselves..

I have created a content type with the required fields and now need to

work

out how to create content directly with SQL from MS Access..

Has anyone ever created content this way or have any idea which tables

need

records created/updated??

Unfortunately the system needs to be able to be updated from the

Drupal site

and from the MS Access application so imports and feeds won't work..

Thanks for any ideas or help..

-- [ Drupal support list | http://lists.drupal.org/ ]

On 7 June 2012 17:24, Earnie Boyd earnie@users.sourceforge.net wrote:

On Thu, Jun 7, 2012 at 11:22 AM, Metzler, David metzlerd@evergreen.edu wrote:

...

Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.

He is asking how to update the tables created by the MSACCESS tool in an external MySql DB. You can connect to the external DB with Drupal and use Drupal to display and update the data. You don't need to create Drupal nodes to do that.

@Ernie - That was where we started but since we are not the worlds

greatest programmers we are looking to switch it the other way around and get MS Access to work on the Drupal tables.. This way we can use "Views", "Rules", "References" and all the other available Drupal functionality as well..

Hi Dave and Ernie,

Maybe a bit more explanation will help..

We have a home baked MS access billing application that we use to generate and manage the bookings and bill based on those bookings.. The administration in managing all the bookings is getting to be too much so we want our customers to be able to manage and edit their bookings through our website..

This needs to be a two way process so bookings can still be managed through the MS Access side but can also be managed through the web interface..

It seemed easiest to create a new content type with all the required fields and references through Drupal and then get MS access to manage the same data tables in the mysql database.. The would mean creating, updating and deleting bookings nodes directly in the database from MS access..

From your comments it sounds like you are saying that its not as straight

forward as that to manage Drupal's data.. I thought that by simply updating the "node" table along with all the "field" and "revision" tables that we could add or edit booking nodes from the backend while durpal managed it from the front..

Is this not the case??

@Dave - Fonena looks interesting and might solve the next part of the system I will be trying to create which is to show the users their billing data that is being generated by the MS Access application, obviously here a report/view is all that's needed because they don't need the ability to change the data..

Thanks..

On 7 June 2012 16:22, Metzler, David metzlerd@evergreen.edu wrote:

Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.

Dave

On Thu, Jun 7, 2012 at 1:00 PM, Earnie Boyd earnie@users.sourceforge.net wrote:

On Thu, Jun 7, 2012 at 12:46 PM, Wipe_Out wrote:

...

Hi Dave and Ernie,

Maybe a bit more explanation will help..

We have a home baked MS access billing application that we use to generate and manage the bookings and bill based on those bookings.. The administration in managing all the bookings is getting to be too much so we want our customers to be able to manage and edit their bookings through our website..

This needs to be a two way process so bookings can still be managed through the MS Access side but can also be managed through the web interface..

It seemed easiest to create a new content type with all the required fields and references through Drupal and then get MS access to manage the same data tables in the mysql database.. The would mean creating, updating and deleting bookings nodes directly in the database from MS access..

From your comments it sounds like you are saying that its not as straight forward as that to manage Drupal's data.. I thought that by simply updating the "node" table along with all the "field" and "revision" tables that we could add or edit booking nodes from the backend while durpal managed it from the front..

Is this not the case??

You don't want to update the node tables directly.  You would miss out on the hook implementations.  You need to create a node object and use node_save() to do it.  You could manage this by creating a custom module to push and pull the changes to an external DB for the ms

or separate table, it doesn't have to be an external DB.

access entries via a hook_cron implementation.  There may be a module already to do this but I don't know.

On Thu, Jun 7, 2012 at 1:23 PM, Wipe_Out wipe_out@users.sourceforge.net wrote:

...

You don't want to update the node tables directly.  You would miss out on the hook implementations.  You need to create a node object and use node_save() to do it.  You could manage this by creating a custom module to push and pull the changes to an external DB for the ms access entries via a hook_cron implementation.  There may be a module already to do this but I don't know.

What are the hook implementations you mention specifically??

I can only point you to the technical documentation. http://api.drupal.org/api/drupal/includes%21module.inc/group/hooks/7

If the data tables are all updated correctly will drupal not see it as a node?

I doubt that you'll be able to update the tables correctly but not all that is Drupal will not know that you created the node so "correctly" is suspect to begin with.

As mentioned we aren't programmers so this seemed like the easier option..

Then please hire someone to do it. See consulting@drupal.org and http://groups.drupal.org/jobs for posting an opportunity.

My concern with trying to write something to keep two sets of data "syncronised" is managing the whole syncronisation process and issues with changes happening on both sides causing a "split brain" scenario.. If both access interfaces are editing the same data this is reduced.. No different to why MySQL replication is not often attempted as master-master because the sync is not easy to do reliably..

But the syncing scenario is the only reliable way to do it, IMO. Or, teach the administrators to use the Drupal interface to do their changes instead of MS Access.

On 7 June 2012 18:43, Metzler, David metzlerd@evergreen.edu wrote:

I agree with Earnie, the best advice is to hire a developer/consultant to work through this with/for you. Or to convince your Access users to modify the data in drupal instead of the access.

Ok, working on that theory would this be a more feasible/logical approach based on our level of code programming ability..

1. In order to create the bookings we generate a CSV from MS Access and use something like the "Feeds" module to import it.. This should handle the node creation for bulk generated bookings right??

2. Users then make changes to the bookings through Drupal rather than access which solves that aspect..

3. For billing purposes I assume its not a problem to query the data from the Drupal tables from access..

This leaves one potential issue..

In the booking there is a "Date" field for booked time and a second "Date" field for actual time (both fields part of the same node so would be created when its generated)..

We have an external interface used for clocking actual start and end times which would need to update the values of the actual time field.. Would it be a problem to only UPDATE these DateTime values from an interface outside of Drupal??

Thanks..

On 7 June 2012 21:42, Metzler, David metzlerd@evergreen.edu wrote:

**

That sounds more feasible. Note that drupal date and time fields are likely unix timestamps, so you my run into troubles converting from access times to date times, but provided you can get that date math to work out, it seems plausible. ****

---

Thanks for your input David..

The "Date" module saves as a Datetime field so the format shouldn't be a problem.. Also the "clocking" interface is php based and the plan is to change the query to update the correct table..

Thanks again..

But the Drupal dates are an int field. You'll need to convert the integer time into the Datetime format and vice versa. See http://us.php.net/time for information on what Drupal stores.

Hi Earnie The Date module creates the fields as "datetime"..

From phpMyAdmin

`field_booking_actual_value` datetime DEFAULT NULL, `field_booking_actual_value2` datetime DEFAULT NULL,

Today I have been looking at the node_save and drupal_execute options for creating nodes programatically.. It doesn't look too difficult (famous last words) so may be a solution to working with the drupal data and creating or updating nodes from our clocking interface..

The MSAccess issue is resolved now by using a feed to import the bookings from a CSV..