Insert, from Excel UserForm into Access DB

[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---

 

[Steve Sanford]

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

 

[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

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---

 

[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.)

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---

 

[ryguy7272]

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

Regards,
Ryan---

--
RyGuy

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.)

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---

 

[Steve Sanford]

You're welcome

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

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

Regards,
Ryan---

--
RyGuy

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.)

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---