Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert data into ACCESS from EXCEL run VBA.

Posted on 2007-08-10
4
Medium Priority
?
361 Views
Last Modified: 2013-11-25
I want to insert data from an Excel range into an ACCESS database and run the procedure from Excel.  After some research, I came across a varition of below but I keep getting an "Automation Error".  But as far can I can tell, the code is correct.  Any suggestions? TESTINGUPLOAD is the name of the range in the Excel workbook.  It gets to the Execute statement and then the error appears.

Private Sub cmdOpenDatabase_Click()
Dim conn As ADODB.Connection
Dim strSQL As String

strSQL = "INSERT INTO tblTESTING SELECT * FROM OPENROWSET([EXCEL 8.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=Yes].[TESTINGUPLOAD]) "

    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
    conn.ConnectionString = strDatabaseTestingpath
    conn.Open
    conn.Execute (strSQL)
    conn.Close


End Sub
0
Comment
Question by:ssmith94015
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
sah18 earned 1000 total points
ID: 19675406
What versions of Access and Excel are you using for this?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 1000 total points
ID: 19676076
Can you call for headers as field names and still load a range?
0
 

Author Comment

by:ssmith94015
ID: 19677878
Both are 2003 versions, but I think i found an alternative in using automation.  So this is moot, I am going to split point however because you both had the grace to respond.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19679034
Thanks.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Implementing simple internal controls in the Microsoft Access application.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question