Insert, from Excel UserForm into Access DB

R

ryguy7272

About two weeks ago, or thereabouts, someone gave me this code (below):

Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrName = Me.txtBoxName.Value
strSQL = "INSERT INTO Names [Name] VALUES '" & usrName & "'"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

I can’t seem to find that post now, so I guess I will start another.
Anyway, I think the code is mostly in tact, but I get an error that reads
‘Syntax error in INSERT INTO statement’.

This line is yellow:
recordSet.Open strSQL, dbConnection

I’m not sure what is causing the error, but I’d appreciate it if someone can
tell me.


Regards,
Ryan---
 
R

ryguy7272

Thanks a ton Steve!! It works great!! Just one more thing, can I add some
syntax to prompt the user to either enter more information or exit? Right
now, I click the CommandButton and the data goes from the Excel UserForm to
the Table tblClients in Access, but it is impossible to determine that
anything happens unless you go to look at the tblClients in Access.
Something a bit more interactive woudl be great!!

Here is my code now:
Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrClient = Me.txtBoxName.Value

strSQL = "INSERT INTO tblClients ([Clients]) VALUES ('" & usrClient & "')"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

It's interesting that I changed from the reserved word 'Name' to the word
Clients and the process works. I'm not sure why there was not a better
description of the error before.

Anyway, if I could add a bit of syntax to make the experience more
interactive, that woudl be great.


Kind regards,
Ryan---


--
RyGuy


Steve Sanford said:
The syntax looks wrong. Try this:

strSQL = "INSERT INTO Names ([Name]) VALUES ('" & usrName & "')"


Also, *Name* is a reserved word (and not very descriptive) in Access/SQL.
Here is a list of words that should not be used for object names:

http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
About two weeks ago, or thereabouts, someone gave me this code (below):

Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrName = Me.txtBoxName.Value
strSQL = "INSERT INTO Names [Name] VALUES '" & usrName & "'"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

I can’t seem to find that post now, so I guess I will start another.
Anyway, I think the code is mostly in tact, but I get an error that reads
‘Syntax error in INSERT INTO statement’.

This line is yellow:
recordSet.Open strSQL, dbConnection

I’m not sure what is causing the error, but I’d appreciate it if someone can
tell me.


Regards,
Ryan---
Click to expand...
Click to expand...
 
S

Steve Sanford

Ryan,

How interactive do you want it to be? The simplest is to use a message box:

<snip>
'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

Msgbox "Record added.."

End Sub


Another way is to pop up another form that asks if you want to add more or
close the form.

You could add code that opens the mdb and checks that the name was added
before telling you that the name was added (or not), either with the message
box or the popup form.

You should also add an error handler....if the database is moved or gets
corrupted, the error is handled gracefully.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Thanks a ton Steve!! It works great!! Just one more thing, can I add some
syntax to prompt the user to either enter more information or exit? Right
now, I click the CommandButton and the data goes from the Excel UserForm to
the Table tblClients in Access, but it is impossible to determine that
anything happens unless you go to look at the tblClients in Access.
Something a bit more interactive woudl be great!!

Here is my code now:
Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrClient = Me.txtBoxName.Value

strSQL = "INSERT INTO tblClients ([Clients]) VALUES ('" & usrClient & "')"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

It's interesting that I changed from the reserved word 'Name' to the word
Clients and the process works. I'm not sure why there was not a better
description of the error before.

Anyway, if I could add a bit of syntax to make the experience more
interactive, that woudl be great.


Kind regards,
Ryan---


--
RyGuy


Steve Sanford said:
The syntax looks wrong. Try this:

strSQL = "INSERT INTO Names ([Name]) VALUES ('" & usrName & "')"


Also, *Name* is a reserved word (and not very descriptive) in Access/SQL.
Here is a list of words that should not be used for object names:

http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
About two weeks ago, or thereabouts, someone gave me this code (below):

Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrName = Me.txtBoxName.Value
strSQL = "INSERT INTO Names [Name] VALUES '" & usrName & "'"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

I can’t seem to find that post now, so I guess I will start another.
Anyway, I think the code is mostly in tact, but I get an error that reads
‘Syntax error in INSERT INTO statement’.

This line is yellow:
recordSet.Open strSQL, dbConnection

I’m not sure what is causing the error, but I’d appreciate it if someone can
tell me.


Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Both recommendations worked excellent!!!
Thanks for responding Steve!!

Regards,
Ryan---

--
RyGuy


Steve Sanford said:
Ryan,

How interactive do you want it to be? The simplest is to use a message box:

<snip>
'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

Msgbox "Record added.."

End Sub


Another way is to pop up another form that asks if you want to add more or
close the form.

You could add code that opens the mdb and checks that the name was added
before telling you that the name was added (or not), either with the message
box or the popup form.

You should also add an error handler....if the database is moved or gets
corrupted, the error is handled gracefully.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Thanks a ton Steve!! It works great!! Just one more thing, can I add some
syntax to prompt the user to either enter more information or exit? Right
now, I click the CommandButton and the data goes from the Excel UserForm to
the Table tblClients in Access, but it is impossible to determine that
anything happens unless you go to look at the tblClients in Access.
Something a bit more interactive woudl be great!!

Here is my code now:
Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrClient = Me.txtBoxName.Value

strSQL = "INSERT INTO tblClients ([Clients]) VALUES ('" & usrClient & "')"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

It's interesting that I changed from the reserved word 'Name' to the word
Clients and the process works. I'm not sure why there was not a better
description of the error before.

Anyway, if I could add a bit of syntax to make the experience more
interactive, that woudl be great.


Kind regards,
Ryan---


--
RyGuy


Steve Sanford said:
The syntax looks wrong. Try this:

strSQL = "INSERT INTO Names ([Name]) VALUES ('" & usrName & "')"


Also, *Name* is a reserved word (and not very descriptive) in Access/SQL.
Here is a list of words that should not be used for object names:

http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

About two weeks ago, or thereabouts, someone gave me this code (below):

Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrName = Me.txtBoxName.Value
strSQL = "INSERT INTO Names [Name] VALUES '" & usrName & "'"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

I can’t seem to find that post now, so I guess I will start another.
Anyway, I think the code is mostly in tact, but I get an error that reads
‘Syntax error in INSERT INTO statement’.

This line is yellow:
recordSet.Open strSQL, dbConnection

I’m not sure what is causing the error, but I’d appreciate it if someone can
tell me.


Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

You're welcome

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Both recommendations worked excellent!!!
Thanks for responding Steve!!

Regards,
Ryan---

--
RyGuy


Steve Sanford said:
Ryan,

How interactive do you want it to be? The simplest is to use a message box:

<snip>
'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

Msgbox "Record added.."

End Sub


Another way is to pop up another form that asks if you want to add more or
close the form.

You could add code that opens the mdb and checks that the name was added
before telling you that the name was added (or not), either with the message
box or the popup form.

You should also add an error handler....if the database is moved or gets
corrupted, the error is handled gracefully.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Thanks a ton Steve!! It works great!! Just one more thing, can I add some
syntax to prompt the user to either enter more information or exit? Right
now, I click the CommandButton and the data goes from the Excel UserForm to
the Table tblClients in Access, but it is impossible to determine that
anything happens unless you go to look at the tblClients in Access.
Something a bit more interactive woudl be great!!

Here is my code now:
Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrClient = Me.txtBoxName.Value

strSQL = "INSERT INTO tblClients ([Clients]) VALUES ('" & usrClient & "')"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

It's interesting that I changed from the reserved word 'Name' to the word
Clients and the process works. I'm not sure why there was not a better
description of the error before.

Anyway, if I could add a bit of syntax to make the experience more
interactive, that woudl be great.


Kind regards,
Ryan---


--
RyGuy


:

The syntax looks wrong. Try this:

strSQL = "INSERT INTO Names ([Name]) VALUES ('" & usrName & "')"


Also, *Name* is a reserved word (and not very descriptive) in Access/SQL.
Here is a list of words that should not be used for object names:

http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

About two weeks ago, or thereabouts, someone gave me this code (below):

Private Sub btnSendToDB_Click()
Dim usrName As String
Dim dbConnection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim strSQL As String

Set dbConnection = New ADODB.Connection
Set recordSet = New ADODB.recordSet
'The variable that'll hold the data within txtBoxName
usrName = Me.txtBoxName.Value
strSQL = "INSERT INTO Names [Name] VALUES '" & usrName & "'"

dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
=C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb"
dbConnection.CursorLocation = adUseClient

' This will open up the recordSet, using the SQL statement and the
dbConnection - Connection.
recordSet.Open strSQL, dbConnection

'Close of the connection.
dbConnection.Close
Set dbConnection = Nothing

End Sub

I can’t seem to find that post now, so I guess I will start another.
Anyway, I think the code is mostly in tact, but I get an error that reads
‘Syntax error in INSERT INTO statement’.

This line is yellow:
recordSet.Open strSQL, dbConnection

I’m not sure what is causing the error, but I’d appreciate it if someone can
tell me.


Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 

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

Simple Insert Into... 28
SQL -- VBA 3
ADO Error in Access calling from Excel 6
Problem with Yes/No DataType. Also, Split a DB. How? 6
trying to add data from a form to a table 8
Error 3001 recordset find sql 2
import all sheets from a closed workbook 2
Insert, Update and Delete. Can somebody please help me? 3

Top