Using VBA to Import from Excel to Access

[Wheat]

All,

I'm currently building a database to store information
regarding quality checks. My QA dept uses an excel file
to "grade" work. Once a grade has been completed, qa
clicks a button to have the information stored on a server
for viewing by the gradee.

I would like to use a vba function to import the required
data from Excel to Access rather than manually importing
the data for each quality check (up to 100+ per day).

I'm using the following code to insert the information I
need:

Public Function fAddRecordToAccess()

Dim ws As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intBPID As Integer
Dim strAcct As String
Dim strCSR As String
Dim intQA As Integer
Dim intScore As Integer
Dim dtmDate As Date


intBPID = Sheet1.Cells(1, 4).Value
strAcct = Sheet1.Cells(4, 4).Value
strCSR = Sheet1.Cells(4, 6).Value
intQA = Sheet1.Cells(6, 12).Value
intScore = Sheet1.Cells(2, 12).Value
dtmDate = Sheet1.Cells(4, 11).Value


Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("S:\Training Team\Training
Analyst\Testing\Import Test.mdb", , False, True)
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

strSQL = "INSERT INTO tblTest Values
(intBPID,strAcct,strCSR,intQA,intScore,dtmDate)"

Set ws = Nothing
Set db = Nothing
Set rs = Nothing

End Function

I'm not sure if I'm even heading in the right direction or
if I'm totally off since this is all new to me. As of
right now, this code does nothing. Any help is
appreciated.

Thanks in advance

 

[John Nurick]

Hi Wheat,

Comments inline.

All,

I'm currently building a database to store information
regarding quality checks. My QA dept uses an excel file
to "grade" work. Once a grade has been completed, qa
clicks a button to have the information stored on a server
for viewing by the gradee.

I would like to use a vba function to import the required
data from Excel to Access rather than manually importing
the data for each quality check (up to 100+ per day).

I'm using the following code to insert the information I
need:

Public Function fAddRecordToAccess()

Dim ws As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intBPID As Integer
Dim strAcct As String
Dim strCSR As String
Dim intQA As Integer
Dim intScore As Integer
Dim dtmDate As Date


intBPID = Sheet1.Cells(1, 4).Value
strAcct = Sheet1.Cells(4, 4).Value
strCSR = Sheet1.Cells(4, 6).Value
intQA = Sheet1.Cells(6, 12).Value
intScore = Sheet1.Cells(2, 12).Value
dtmDate = Sheet1.Cells(4, 11).Value


Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("S:\Training Team\Training
Analyst\Testing\Import Test.mdb", , False, True)

Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

You don't need the recordset.

strSQL = "INSERT INTO tblTest Values
(intBPID,strAcct,strCSR,intQA,intScore,dtmDate)"

VBA doesn't interpolate (substitute) variables into strings like that.
You have to concatenate them explicitly and enclose string values in
single quotes (double quotes are optional)

strSQL = "INSERT INTO tblTest VALUES (" _
& intBPID & ",'" & strAcct & "','" _
& strCSR & "'," & intQA & "," & intScore _
& ",#" & Format(dtmDate, "mm/dd/yyyy") _
& "#);"

Then execute the query:

db.Execute strSQL, dbFailOnError

and finally tidy up

db.Close

 

[Wheat]

John,

Thank you for your help!

-----Original Message-----
Hi Wheat,

Comments inline.

All,

I'm currently building a database to store information
regarding quality checks. My QA dept uses an excel file
to "grade" work. Once a grade has been completed, qa
clicks a button to have the information stored on a server
for viewing by the gradee.

I would like to use a vba function to import the required
data from Excel to Access rather than manually importing
the data for each quality check (up to 100+ per day).

I'm using the following code to insert the information I
need:

Public Function fAddRecordToAccess()

Dim ws As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intBPID As Integer
Dim strAcct As String
Dim strCSR As String
Dim intQA As Integer
Dim intScore As Integer
Dim dtmDate As Date


intBPID = Sheet1.Cells(1, 4).Value
strAcct = Sheet1.Cells(4, 4).Value
strCSR = Sheet1.Cells(4, 6).Value
intQA = Sheet1.Cells(6, 12).Value
intScore = Sheet1.Cells(2, 12).Value
dtmDate = Sheet1.Cells(4, 11).Value


Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("S:\Training Team\Training
Analyst\Testing\Import Test.mdb", , False, True)

Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

You don't need the recordset.

strSQL = "INSERT INTO tblTest Values
(intBPID,strAcct,strCSR,intQA,intScore,dtmDate)"

VBA doesn't interpolate (substitute) variables into strings like that.
You have to concatenate them explicitly and enclose string values in
single quotes (double quotes are optional)

strSQL = "INSERT INTO tblTest VALUES (" _
& intBPID & ",'" & strAcct & "','" _
& strCSR & "'," & intQA & "," & intScore _
& ",#" & Format(dtmDate, "mm/dd/yyyy") _
& "#);"

Then execute the query:

db.Execute strSQL, dbFailOnError

and finally tidy up

db.Close

Set ws = Nothing
Set db = Nothing
Set rs = Nothing

End Function

I'm not sure if I'm even heading in the right direction or
if I'm totally off since this is all new to me. As of
right now, this code does nothing. Any help is
appreciated.

Thanks in advance

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.