Re: Cant get SQL INSERT code to work

From: MGFoster (me_at_privacy.com)
Date: 05/03/04 

Date: Mon, 03 May 2004 19:35:24 GMT

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure you can use SQL to add data to an Excel file from another
Excel file. But, if you can, then use the correct syntax for INSERT
INTO statements. From Access Help file (covers JET SQL):

INSERT INTO target [(field1[, field2[, ...]])]
     VALUES (value1[, value2[, ...])

Where 'target' is the name of the table.
Where 'field' is the name of the column in the table.
Where 'value' is the value that will be placed in the column in the
table.

Therefore, this statement won't work:

   INSERT INTO (AAA) VALUES ('test')

because it doesn't have the table name. 

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQJae/4echKqOuFEgEQKfbwCgl5/J0hmtZrqLpX5I8gCgb6rhwV0AoM72
UQ4IbfoI8Xf5756sGiLQXA/d
=Y8qK
-----END PGP SIGNATURE-----
Todd Huttenstine wrote:
> Private Sub CommandButton1_Click()
>     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 [Sheet1] " & "VALUES ('test')"
>     'szSQL = "INSERT INTO (AAA) VALUES ('test')"
>     strSQL = "INSERT INTO Sheet1 (AAA, BBB) VALUES ('test1','test2')"
> 
> 
> 
>     ' Create and open the Connection object.
>     Set objConn = New ADODB.Connection
>     objConn.Open szConnect
>     ' Execute the insert statement.
>     objConn.Execute szSQL, , adCmdText
>     ' Close and destroy the Connection object.
>     objConn.Close
>     Set objConn = Nothing
> 
> '    You 'll need to modify the connection string Data Source to point to
> your
> 'Excel file and modify the SQL statement to fit your data.
> End Sub
> 
> Above is my code...
> I am working from within Microsoft Excel.  I have created an Excel
> spread*** called Employees.xls.  I have put all my code in a button on the
> spreadhseet.  The spread*** I am trying to make act as a database and add
> a new record is located on C:\TEST.XLS.  In the code above I have also
> referenced this file path correctly I believe...  TEST.XLS has 2 columns of
> data: One is AAA and the other is BBB, which are located in columns A and B
> consecutively .  I have tried the following 3 SQL lines and none of them
> work:
> 
>     'szSQL = "INSERT INTO [Sheet1] " & "VALUES ('test')"
>     'szSQL = "INSERT INTO (AAA) VALUES ('test')"
>     strSQL = "INSERT INTO Sheet1 (AAA, BBB) VALUES ('test1','test2')"
> 
> What do I need to do to make this work?
> 
> 
> 
> "MGFoster" <me@privacy.com> wrote in message
> news:IWvlc.4445$Hs1.2513@newsread2.news.pas.earthlink.net...
> 
>>-----BEGIN PGP SIGNED MESSAGE-----
>>Hash: SHA1
>>
>>1) Since you're working from an Access application & connecting to
>>another Access file, you don't need to do anything with ODBC.
>>
>>2)  You're SQL statement is incorrect.  Try this:
>>
>>szSQL = "INSERT INTO [Employees] " & _
>>         "VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')"
>>
>>Usually it is a good idea to put the column names that will receive the
>>data, especially when the table has more columns than are in the VALUES
>>clause.  E.g.:
>>
>>INSERT INTO Employees (LastName, FirstName, Address, Province)
>>VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')
>>
>>I'm not sure, but, I don't think you need the "Or adExecuteNoRecords"
>>after the adCmdText.
>>
>>I've never seen the connection string property "Extended Properties"
>>used before.  Is this to indicate that the "table" Employees is an Excel
>>spread***?  If you're trying to directly update an Excel s/s, you
>>haven't followed the directions given at the bottom of your code:
>>"You'll need to modify the connection string Data Source to point to
>>your Excel file . . . ."  To me this means you have to set the Data
>>Source property to the UNC of the Excel s/s:
>>
>>"Data Source=C:\My Documents\MyExcel.xls;"
>>
>>-- 
>>MGFoster:::mgf00 <at> earthlink <decimal-point> net
>>Oakland, CA (USA)
>>
>>-----BEGIN PGP SIGNATURE-----
>>Version: PGP for Personal Privacy 5.0
>>Charset: noconv
>>
>>iQA/AwUBQJaLNYechKqOuFEgEQKXogCfc6MyEnPPdREr+xsgH2SBuW/0qZgAn0Q9
>>gadpzaduiLjrl8QUfaRH2KKN
>>=del4
>>-----END PGP SIGNATURE-----
>>
>>
>>Todd Huttenstine wrote:
>>
>>
>>>You need square brackets [] around column/table names that have spaces:
>>>
>>>[Employee Database]
>>>[Employe ID]
>>>
>>>MGFoster suggested  I put brackets around my table name(above
> 
> instructions).
> 
>>>I did that.  For some reason it is still not working.  Do I need to go
> 
> into
> 
>>>ODBC management and do anything?  This is my first time ever trying to
>>>manipulate a database with code.  Below is my new modified code.  I get
> 
> an
> 
>>>error on line     objConn.Execute szSQL, , adCmdText Or
> 
> adExecuteNoRecords.
> 
>>>Any help is greatly appreciated.
>>>
>>>
>>>
>>>    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:\Employees.mdb;" & "Extended Properties=Excel 8.0;"
>>>    ' Create the SQL statement.
>>>    szSQL = "INSERT INTO [Employees VALUES] ('Hetland', 'Camilla',
>>>'Hagabakka 24', 'Sandnes')"
>>>    ' 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
>>>
>>>'    You 'll need to modify the connection string Data Source to point
> 
> to
> 
>>>your
>>>'Excel file and modify the SQL statement to fit your data.
>>
> 
>