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