ADO SQL Code Problems (Continued from earlier post)

  • Thread starter Todd Huttenstine
  • Start date

T

Todd Huttenstine

The below code is what I found in Google groups. Here is the link to the
post below.
http://groups.google.com/groups?q=w...TF-8&[email protected]&rnum=5

I have tried for days to get this to work. I have even sat my source
spreadsheet in ODBC administrator as a source. I also added the reference
as described below. I have no idea what I am doing wrong. Can anyone
please help?

My source spreadsheet I am trying to add a line to is C:\TEST.XLS. It has 2
columns A and B. ColumnA header is AAA and ColumnB is BBB. I simply want
to have this code in another spreadhseet and have it add a record to the
file.

If you have tabular data you can write to a closed workbook using ADO.
Here's an example procedure. Note that you'll need to add a reference to the
Microsoft ActiveX Data Objects 2.x library from your project in order to run
this.

Public Sub WorksheetInsert()
Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Files\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
' Create the SQL statement.
szSQL = "INSERT INTO <BookLevelName> " & _
"VALUES('Val1', 'Val2', Val3, Val4);"
' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL, , adCmdText Or adExecuteNoRecords
' Close and destroy the Connection object.
objConn.Close
Set objConn = Nothing
End Sub


Heres my code that I modified from above.

Public Sub WorksheetInsert()
Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TEST.XLS;" & _
"Extended Properties=Excel 8.0;"
' Create the SQL statement.
szSQL = "INSERT INTO <BookLevelName> " & _
"VALUES('TestVal1', 'TestVal2');"
' Create and open the Connection object.
Set objConn = New ADODB.Connection
objConn.Open szConnect
' Execute the insert statement.
objConn.Execute szSQL, , adCmdText Or adExecuteNoRecords
' Close and destroy the Connection object.
objConn.Close
Set objConn = Nothing
End Sub


Does anyone know what I am doing wrong?
 
Ad

Advertisements

C

Chris2

Todd Huttenstine,

Well, without knowing the errors that are occuring, it's difficult to know
what is being done wrong.

However, I spot something of interest in the example code.

szSQL = "INSERT INTO <BookLevelName> " & _
"VALUES('TestVal1', 'TestVal2');"

<BookLevelName> must be replaced, I believe by the name of the
spreadsheet.
I think the format will resemble either:

1) [pathname\filename].worksheetname

2) worksheetname

I repeat, I *think* it will resemble that, you'll want to do some research
on how to name a spreadsheet in place of a tablename.

Note: The groups microsoft.public.access.modulesdaovba.ado or
microsoft.public.excel.programming may know more.


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Insert SYntax 3
Cant get SQL INSERT code to work 8
Query Code Problem 1
SQL Update 1
Write to closed workbook code 4
SQL not importing records from ACCDB 2
SQL problem - Jamie 4
SQL String 1

Top