VBScript to write data to FM database?

[Paul McGrath]

Hi everyone, new to FM and new to here.

We run a mixed installation of Mac and Windows computers in a Tiger server OD with a SAMBA domain.

My Mac engineer colleague has got ARD to put data about the Macs into a FM asset database, such as serial number, mac address etc...

I use a tool called BGINFO to do the same to an access database on a share on the server.

I have a vbscript logon script which maps drives and executes bginfo and does other things.

My question is.... how easy is it to add a bit of vbscript to take some WMI data and write it to fields in the FM asset table?

I would obviously have to query the database first to check if the workstation exists if not write a whole new record otherwise update the existing record.

Has anyone tried this and would be willing to share some sample code for me to work on?

What options do I need to check in filemaker to make sure the vbscript can connect?

Thanks

Paul

[normanicus]

Go here: http://www.connectingdata.com/downloads.htm

and download 'VBscripts_repository.fp7'

[Paul McGrath]

Thanks, did that and searched through scripts but couldn't find an example how to write to a FM database.

If you know of one can you point me in the right direction?

thanks

Paul

[aholtzapfel]

This saved as a .vbs will look for a filemaker file, copy it to a local path, and execute a script.

'Creates Filemaker object

Set objFM = WScript.CreateObject ("FMPRO.Application")

'Creates File System Object

Set fs = CreateObject("Scripting.FileSystemObject")

'Check if the file exists

If fs.FileExists("C:local pathFM_File.fp7") then

'nothing

Else

'Copy File to Local Computer

 fs.CopyFile "network pathFM_File.fp7", "C:local pathFM_File.fp7"

 Wscript.Sleep 2000

End If

Set objFMfiles = objFM.Documents.Open("C:local pathFM_File.fp7","admin","") 'open FM file   (filename,username,password)

objFMfiles.DoFMScript ("Import") ' call this FM script

Wscript.Sleep 1000

WshShell.AppActivate "FileMaker Pro"

WScript.DisconnectObject(fs)

WScript.DisconnectObject(objFM)

WScript.Quit(1)

Couple of notes about doing this:

• * You cannot open a shared filemaker file from a VBS script (you can connect to one) so if you are running the script before the target file is open you need to open a local file. (the script in the local file can execute a script in a shared file.)
  
  * You can only use vbs to execute a filemaker script. (so to get info into filemaker you need to import it)
  
  * There is a bug in the Filemaker Object model (do not use script folders in the filemaker file you are calling the script from, if you do, the vbs call for the script may call the wrong one.)
  

I hope this helps a little.

Allen

[Søren Dyhr]

* You can only use vbs to execute a filemaker script. (so to get info into filemaker you need to import it)

I was about to say so, but I'm usually on mac's where we have applescript pretty well fledged for such a task, but seemed to recall this.

However have I seen in Wim's site you can use the webviewer for returning stuff as well.

--sd

[aholtzapfel]

A web viewer could be used to access data but to populate fields you would still need to run a Filemaker Script. (Import vs Scraping a Web page)

Not a bad idea, depending on your needs.

[Paul McGrath]

Thank you both for your advice... think it is more involved than I expected.

[Wim Decorte]

Yes it is. The FM ActiveX model does not allow to set data into a field or even to pass a parameter to a script (which would solve some of this). I've been lobbying for this for ages now so please enter it as a suggestion on the FMI website.

You have a few more options:

- use a local ODBC connection to your FM file to push the data in

- use FileMaker Server Advanced and ODBC or XML into that.

HTH

Wim

[Paul McGrath]

We are running FM 9 Server Advanced so I thought I should just be able to query by ODBC remotely but I can't find anything on it.

I have tried to modify sample vbscripts which do ODBC stuff with MySQL and MSSQL but I juts get connection errors. When I have setup my xsan I'll come back to it.