Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy MSAccess Table to SQL Server with VBScript Unspecified Error 2

Status
Not open for further replies.
grnzbra

grnzbra

Programmer
Mar 12, 2002
1,273
US
I am trying to copy tables from MS Access to SQL Server and have an vbscript .hta. When I run this I get an error message that says "Unspecified Error" on the cmd.Execute line. Any ideas what is wrong here?

Code: 
<!DOCTYPE html>
<html>
<TITLE>Access to SQL Short Version</TITLE>
<HEAD>
    <HTA:APPLICATION 
	ID="AccessToSQLShortVersion" 
	APPLICATIONNAME="AccessToSQLShortVersion"
	SHOWINTASKBAR="yes"
	SINGLEINSTANCE="yes"
	SCROLL="yes"
  >
</HEAD>

<body>
 <form name="F1" id="F1">

  <table name="T1" id="T1">
   <tr>
    <td></td>
        <input type=button name="Run" id="Run" onclick=ButtonPress1() value="Run"></input>
    <td></td>
   </tr>
   <tr>
  </table>
  
 </form>
 
</body>

<script language="VBScript">
Sub ButtonPress1()
'Set Up Access Connection
    set acccn = CreateObject("ADODB.Connection")
    acccnstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:/Documents and Settings/myname/My Documents/LIMA.TESTDB.mdb"
    acccn.connectionString = acccnstr
    acccn.Open
'msgbox "acccn open"
    set sqlcn = CreateObject("ADODB.Connection")
    sqlcnstr = "Provider=SQLOLEDB;Data Source=192.xxx.y.zz;Trusted_Connection=Yes;Initial Catalog=SQLSERVER_DB;User ID=ME;Password=THEM;"
    sqlcn.ConnectionString = sqlcnstr
    sqlcn.Open
'msgbox "tblcn open"
    set cmd = CreateObject("ADODB.Command")
    cmd.CommandType = 1
    cmd.ActiveConnection = acccn
'msgbox "cmd created"
    cmd.CommandText = "SELECT * INTO tblwGUIDSource IN '' " & sqlcnstr & " FROM tblwGUIDSource"
msgbox "about to execute command"
    cmd.Execute
'acccn.ExecuteNonQuery()
set cmd = nothing
sqlcn.Close()
set sqlcn = nothing
acccn.Close()
set acccn = nothing
End Sub


</script>

</html>
 
Sort by date Sort by votes

First thing I see is the cmd.CommandText doesn't equate to valid SQL syntax:

Code: 
cmd.CommandText = "SELECT * INTO tblwGUIDSource IN '' " & sqlcnstr & " FROM tblwGUIDSource"

evaluates to

Code: 
cmd.CommandText = "SELECT * INTO tblwGUIDSource IN '' Provider=SQLOLEDB;Data Source=192.xxx.y.zz;Trusted_Connection=Yes;Initial Catalog=SQLSERVER_DB;User ID=ME;Password=THEM; FROM tblwGUIDSource"


Also, you can't use two separate ADODB connections and read/write using both of them in a single command. You'll have to read in from one and then write to the other. May want to look at SQL Server DTS packages or one of the other data transfer methods depending on your particular SQL server version.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
Upvote 0 Downvote
Personally, I use two ADODB connections. One as a "connection" and the other as a recordSet. Then iterate the recordSet

Code: 
sqlcnstr = "Provider=SQLOLEDB;Data Source=192.xxx.y.zz;Trusted_Connection=Yes;Initial Catalog=SQLSERVER_DB;User ID=ME;Password=THEM;"
set objConnection = CreateObject("ADODB.Connection")
objConnection.open sqlcnstr

set objRecordSet = CreateObject("ADODB.RecordSet")
objRecordSet.open strQuery, objConnection, 3, 3

if not (objRecordSet.EOF) then
	strRecords = objRecordSet.GetString
	arrRecords = split(strRecords, vbCr)
	for i = 0 to ubound(arrRecords) - 1
		strRecord = arrRecords(i)
		arrRecords(i) = split(strRecord, vbTab)
	next
end if
objRecordSet.close

Also, there is an error in your HTML. Most browsers will compensate for the error but improper HTML can cause the unexpected results.

Code: 
<td></td>
[red]<td>[/red]<input type=[red]"[/red]button[red]"[/red] name="Run" id="Run" onclick=[red]"[/red]ButtonPress1()[red]"[/red] value="Run">[s]</input>[/s][red]</td>[/red]
<td></td>

-Geates

 
Upvote 0 Downvote
Thanks guys. You've been a big help.
Unfortunately, the security people have things set so that the two databases can't see each other. Otherwise, I'd just create a linked server and run it from a stored procedure.
 
Upvote 0 Downvote
Never heard of having a third recordset that looks at two other recordsets. How is that done?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Alexvb6
  • Locked
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
353
Alexvb6
Alexvb6
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
994
SoftwareRT
SoftwareRT
K
  • Locked
  • Question
VBSCRIPT dont work windows 11 23h2 ???
Replies
2
Views
497
SoftwareRT
SoftwareRT
bdwilcox
  • Locked
  • Question
HTA fails with Syntax Error when working VBScript inserted
Replies
2
Views
345
bdwilcox
bdwilcox
paullem
  • Locked
  • Question
vbscript to read properties of JPEG file
Replies
4
Views
434
paullem
paullem

Part and Inventory Search

Sponsor

Back
Top