Access 2007

[bn2hunt]

Anybody had a chance to look at vbscript with access 2007? There is a new field type called attachment and I would like to be able to add an attachment to this field in a vbscript that I am writing. However when I try to add a record I am getting an error An INSERT INTO query cannot contain a multi-valued field.

Dim cn, cmd, date_time, logfile, mb, model, pcname, results, serial
	date_time = ""
	logfile = "a:\log.txt"
	mb = "1"
	model = "2"
	pcname = "3"
	results = "4"
	serial = "5"
	cnstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\fs01\ho_data_processing\DP_Technical_Services\wipedrive\wipedrive.accdb;Persist Security Info=False;User ID=;Password="
	cmdtext = "INSERT INTO wipedrive (date_time, " & _
		"logfile, " & _
		"mb, " & _
		"model, " & _
		"pcname, " & _
		"results, " & _
		"serial)" & _
		"VALUES ('" & date_time & "', " & _
		"'" & logfile & "', " & _
		"'" & mb & "', " & _
		"'" & model & "', " & _
		"'" & pcname & "', " & _
		"'" & results & "', " & _
		"'" & serial & "')"
	Set cn = CreateObject("adodb.connection") 
	Set cmd = CreateObject("adodb.command")  
	cn.connectionstring = cnstring 
	cn.Open 
	cmd.activeconnection = cn 
	cmd.commandtext = cmdtext 
	
	cmd.Execute
	cn.Close

Any ideas?

bn2hunt

"Born to hunt forced to work

 

[PHV]

Are date_time, logfile, mb, model, pcname, results and serial all defined as text in wipedrive (and NOT multi-value) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bn2hunt]

logfile is defined as attachment, everything else is text.

bn2hunt

"Born to hunt forced to work

 

[PHV]

And no multivalued field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bn2hunt]

I just checked and it looks like the attachment field might be a multivalued field. If I build a query in access there are several fields that are part of the logfile field like
logfile.filedata, logfile.filename, and logfile.filetype.



bn2hunt

"Born to hunt forced to work

 

[PHV]

Wahat about this ?
cmdtext = "INSERT INTO wipedrive (date_time, " & _
"logfile.[filename], " & _
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bn2hunt]

different error message

Microsoft Office Access Database Engine: An INSERT INTO query that contains a multivalued field cannot contain another field.


bn2hunt

"Born to hunt forced to work

 

[PHV]

Yeah, that confirms what I thought:
don't use multivalued fields unless you really have to play with the Microsoft Windows SharePoint Services 3.0 lists ...
Sorry, I can't help you anymore on this issue.

 

[bn2hunt]

I got it to work. If I add the attachment after I create the record it works.

Dim cn, cmd, date_time, logfile, mb, model, pcname, results, serial
	date_time = ""
	logfile = "a:\log.txt"
	mb = "1"
	model = "2"
	pcname = "3"
	results = "4"
	serial = "5"
	cnstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\fs01\ho_data_processing\DP_Technical_Services\wipedrive\wipedrive.accdb;Persist Security Info=False;User ID=;Password="
	cmdtext = "INSERT INTO wipedrive (date_time,mb,model,pcname,results,serial)" & _
		"VALUES ('" & date_time & "', '" & mb & "', '" & model & "', '" & pcname & "', '" & results & "', '" & serial & "')"
	cmdtext1 = "INSERT INTO wipedrive (logfile.[filename])" & "VALUES ('" & logfile & "') where pcname = '" & pcname & "'"
	Set cn = CreateObject("adodb.connection") 
	Set cmd = CreateObject("adodb.command")  
	cn.connectionstring = cnstring 
	cn.Open 
	cmd.activeconnection = cn 
	cmd.commandtext = cmdtext 
	cmd.Execute
	cmd.commandtext = cmdtext1 
	cmd.Execute
	cn.Close

bn2hunt

"Born to hunt forced to work