-1

I am trying to store the values inside an access database from excel using vba.

I have a form in Excel where I get the Inputs from the User.

On VBA, In the Locals window, I could see the values assigned to the object as below,

Name = Mike
Age = 23
Occupation = Student

On the Access database, I have a table called as UserTable which has columns called as ID, Name, Age, Occupation and now how do I pass the values from VBA to Access database.

I would want to add the value record by record and faster at the same time.

Here is where I am getting the error 

strSQL = "PARAMETERS [Name] TEXT(255), [Age] INTEGER, [Occupation] TEXT(255);" _
               & " INSERT INTO UserTable([User Name], [User Age], [User Occupation]);"

Please help me with this on my learning.

Student of the Digital World
  • 2,971
  • 16
  • 62
  • 116
  • Possible duplicate of [Using Excel VBA to run SQL query](https://stackoverflow.com/questions/27385245/using-excel-vba-to-run-sql-query) – Victor K Nov 15 '17 at 16:33
  • @VictorK But my problem is how would you make the record set to copy them into database table. – Student of the Digital World Nov 15 '17 at 16:36
  • You have to look into `ADODB.Connection` and `Insert Into` statement both of which has a plethora of questions here: [1](https://stackoverflow.com/q/40127962/8597922),[2](https://stackoverflow.com/q/1802120/8597922),[3](https://stackoverflow.com/q/25226635/8597922). As you go you should also check how to use parametrized queries. – Victor K Nov 15 '17 at 16:43
  • @VictorK Thanks. I followed your links. I am getting an error in the Insert into statement. I have updated the question, Any Idea where it is going wrong ? – Student of the Digital World Nov 15 '17 at 17:22
  • Actually [here is my answer](https://stackoverflow.com/a/46835253/8597922) to a very similar question for `Insert`. And [this is an answer](https://stackoverflow.com/a/43522802/8597922) that explains parameters. – Victor K Nov 15 '17 at 17:29

1 Answers1

-1

Create a reference to the ADO recordset library

create and open a Connection object to the access database

 Dim Conn as new adodb.connection
  conn.connectionstring = "google for connection string here"
  conn.open

create a string holding an append query (watch out: you need commas between fields and ' around strings)

Dim strSQL as string
StrSql = "INSERT INTO [Usertable] ( Name, Age, Occupation ) "
strsql = strsql & "Values('" & me.Name & "'," & me.age & ",'" & me.occupation & "')"

Use EXECUTE to run this string

conn.execute  strsql
Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • Last time I suggested an unparametrized query here on SO I got [this](https://xkcd.com/327/) as a reply. I guess reasonably so. – Victor K Nov 15 '17 at 16:47
  • If little bobby tables does that in a work environment he won't be in that environment soon. This is excel and access in an office - not wikileaks's russian database of fake news – Harassed Dad Nov 15 '17 at 17:02
  • That's not the point. For example a name such as "Sarah O'Connor" which is realistic will not work if either it is not sanitized or parametrized as `'` will blow up the string . – Victor K Nov 15 '17 at 17:10