Cant get SQL INSERT code to work

  • Thread starter Todd Huttenstine
  • Start date
T

Todd Huttenstine

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.



Todd Huttenstine
 
M

MGFoster

-----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
spreadsheet? 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 said:
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.
Click to expand...
 
T

Todd Huttenstine

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
spreadsheet called Employees.xls. I have put all my code in a button on the
spreadhseet. The spreadsheet 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 said:
-----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
spreadsheet? 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 said:
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.
Click to expand...
Click to expand...
 
M

MGFoster

-----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 said:
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
spreadsheet called Employees.xls. I have put all my code in a button on the
spreadhseet. The spreadsheet 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?



-----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
spreadsheet? 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
Click to expand...
instructions).
I did that. For some reason it is still not working. Do I need to go
Click to expand...
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
Click to expand...
an
error on line objConn.Execute szSQL, , adCmdText Or
Click to expand...
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
Click to expand...
to
your
'Excel file and modify the SQL statement to fit your data.
Click to expand...
Click to expand...
Click to expand...
 
T

Todd Huttenstine

Hey I tried that code and I cant get it to work. Here is what I found from
Google Group historic search:

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

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.


I have been trying to get this code to wrok for the past 3 days and I cannot
get it to work. I do not know what I am doing wrong. I even sat it up as a
data source in ODBC and that didnt work. Do you know what the code needs to
be for me to get this to work?






MGFoster said:
-----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 said:
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
spreadsheet called Employees.xls. I have put all my code in a button on the
spreadhseet. The spreadsheet 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?



-----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
spreadsheet? 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.
Click to expand...
Click to expand...
Click to expand...
 
M

MGFoster

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

I tried this Sub routine using Access 97 & Excel 97 (8.0) & no matter
what I did I couldn't get the procedure to work. The last error I got
was the "query has to be an updateable query."

I ran the Sub from Access to an already created .xls file with a section
named "Insert_Section."

If I were trying to move data from one Excel file to another I'd just
cut & paste.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJfaj4echKqOuFEgEQJqDQCdEWKa77oQpLZJS4381wBb4ZZITHYAn1pU
x6e6xW8V+TcsRwNVKoRPxc6V
=nRE2
-----END PGP SIGNATURE-----
 
T

Todd Huttenstine

Hey thank you for your help.

Can I see the code you used when you got that last error just to see what
you did. I keep getting the same exact error "Automation Error"

Thanks
Todd
 
M

MGFoster

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

I set up a DSN to the Excel file. The Excel s/s had a named section
"Insert_Section" with 4 columns w/ 4 headings "Col1" - "Col4." The
error occurred on the objConn.Execute line.


Public Sub WorksheetInsert()
Dim objConn As ADODB.Connection
Dim szConnect As String
Dim szSQL As String
' Create the connection string.
szConnect = "DSN=ADO_TEST;" & _
"DBQ=C:\tmp\ADO_Test.xls;DriverID=790;" & _
"FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;"
' Create the SQL statement.
szSQL = "INSERT INTO Insert_Section (Col1, Col2, Col3, Col4) " & _
"VALUES('Val1', 'Val2', 3, 4);"
' 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


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJho7IechKqOuFEgEQKmzwCgzgvUM2FTdv3VBYxsH7u7oZ1nMPMAnj8l
NUI3uB3Sg1vaDh3yyN212Gy7
=GZ6Q
-----END PGP SIGNATURE-----
 
O

onedaywhen

Worth pointing out that it doesn't have to be a book level name e.g.

INSERT INTO [BookLevelName] (MyCol1, MyCol2) VALUES (1,2)

It can also be a sheet level name e.g.

INSERT INTO [Sheet1$SheetLevelName] (MyCol1, MyCol2) VALUES (1,2)

or a worksheet name e.g.

INSERT INTO [Sheet1$] (MyCol1, MyCol2) VALUES (1,2)

or a range address e.g.

INSERT INTO [Sheet1$A1:B999] (MyCol1, MyCol2) VALUES (1,2)

FWIW because the square brackets are required, you handle a space in a
worksheet name, including the $ sign, with single quotes e.g.

SELECT Col1, Col2 FROM ['Combined TG and TN$']

--
 

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
SQL not importing records from ACCDB 2
ADO SQL Code Problems (Continued from earlier post) 1
Query Code Problem 1
Write to closed workbook code 4
SQL Update 1
SQL problem - Jamie 4
ADO access to excel workbook on website 1

Top