Unable to import data from excel sheet to a new Table using ".OpenDatabase(filepath)" method in VBA

[SunOffice]

Issue: Unable to import data from excel sheet to a new Table using ".OpenDatabase(filepath)" method in VBA from Excel. Details: As ".OpenDatabase(filepath)" method opens Access dababase in invisible mode, but ".OpenCurrentDatabase" method actually make database visible. I doubt that if other users are working on it, then it ".OpenCurrentDatabase" method can create issue. 1) I would like to know the difference between these two methods when running the codes from Excel VBA window. 2) Please let me know why ".OpenDatabase(filepath)" method is not working (see "Sub Method_2__AddNewRecords_ToAccess()" in the codes). 3) Please help me to tell that what is the best method to import data from excel sheet to a new table of MS Access database, then perfom other SQL queries as well. Note: 1) Please review attached sample Excel VBA program for above situation. 2) I am using Excel 2013 32-bit on Windows 7 64-bit. Thanks in Advanced! Regards, SO

[Micron]

IMHO, if you are in Excel, you are not really importing to Access, you are exporting.
Opendatabase returns a database object that you can manipulate, but it does not actually open it in a window. OpenCurrentDatabase opens a database in an Access window. I would not have thought it would be visible to you because I don't see where you use the Show method, which is what I have to use when opening a workbook from Access if I want to see it. Regardless of which you use, the implications for someone else having the database open are the same. If this is a possiblilty, you might want to establish an (I think) ADODB connection to return the state of the database exlusive property.
"Not working" is really no help at all, since we cannot run the code without the related db and its tables or queries. You would have to be more explicit.
One thing I note is that when declaring an application object, I always use
Dim objSomeObjectVarName as Object
Set objSomeObjectVarName = CreateObject ...
but you are declaring the variable as an Access application, then setting it to an Access Application
Dim oAppAccess As Access.Application
Set oAppAccess = CreateObject...
Not sure what the implications of this would be.

RE: (3), if I wanted to import data into Access from Excel, I would do it from Access. It is easier to test if the workbook is open and you can still use the transferspreadsheet method. You wrote that you wanted to "perform other SQL queries" - I have to assume that you mean within Access, so why not be there when the data is imported? Regardless, make sure the workbook cell formats and destination fields in your table are compatible.

[SunOffice]

Hi Micron,

Thank you for reply!
Actually I am building a report automation tool where Excel is front-end and the Access works as back-end (means all the VBA codes will be in Excel file only).
That's why I am looking for the solution which can run from the Excel VBA Windows.

[SunOffice]

Hi Micron,

Thank you for reply!
Actually I am building a report automation tool where Excel is front-end and the Access works as back-end (means all the VBA codes will be in Excel file only).
That's why I am looking for the solution which can run from the Excel VBA Windows.

If this is a possiblilty, you might want to establish an (I think) ADODB connection to return the state of the database exlusive property.

Yes! I agree that ADODB connection will be much faster and reliable for this kind of report automation tool, but I am getting a hard time to find "Importing data from Excel sheet to Access Table" by using ADODB connection.
Please help me on it with some sample codes.

[ItsMe]

I did not look at your example. I have not used OpenDatabase but it is a method within Excel and OpenCurrentDatabase is a method within Access. You might be able to get what you need using Excel and the OpenDatabase method; not relying on the Access runtime.
https://msdn.microsoft.com/en-us/lib.../ff193543.aspx

[Micron]

[...and OpenCurrentDatabase is a method within Access.]
Actually, the scope is wider than Access. It's considered an MS Office level method.

From MSDN: "For example, you can use the OpenCurrentDatabase method from Microsoft Excel" (copied verbatim - no formatting by me)

Re: "Importing data from Excel sheet to Access Table" - once again, you are importing FROM Excel when you are IN Access, yet you say you want import from Excel while IN Excel. If Excel is your preferred driver, you need to EXPORT from Excel to Access. Once you have the connection part figured out, the TransferSpreadsheet method of the DoCmd object is what you will need to use, regardless of which direction you are going. To prove my point, two of the transport type options are acImport and acExport, so you need to understand my point.

[...by using ADODB connection] - you don't "use" the connection to do this. You create the application object and establish a connection to it, then you manipulate the object by using its native methods - no small task. If you're having difficulty finding info on this, perhaps it's because the preferred method would be to do it the other way around. You want to use reports, which are in Access? Sounds like you want features from Access but drive it from Excel. Kind of like driving a car from the back seat! So I have no sample code because I have never done it this way and probably never will. Sorry.

[SunOffice]

Thank you for reply everyone!

Please review my VBA codes as below which I have written in Excel VBA Window. Now I am looking for a solution with codes which runs the program using ADODB connection. Actually, I am unable to identify that how to use ".TransferSpreadsheet acImport" trick using ADODB codes.

Sub Data_ImportFromExcel_ToAccess()
'  Below code is in Excel VBA Window, everything is running from there.
'  User must add "Microsoft Access xx.x Object Library" reference to Excel VBA Codes


' Note - Below code is working, but I am looking for ADODB method (codes) to perform below steps.
Dim oAppAccess As Access.Application
Dim dbFullFilepath As String            ' Full file path for Master Database in MS Access
Dim xlFullFilePath As String            ' To import data from this Excel file to Database


    dbFullFilepath =  ThisWorkbook.Path & "\" & "Master_DB.mdb"
    xlFullFilePath =  ThisWorkbook.Path & "\" & "1. ScoreCard - Jan 2015.xlsx"

    'Create Access Object
    Set oAppAccess = CreateObject("Access.Application")
    oAppAccess.OpenCurrentDatabase dbFullFilepath                               'Note - below command actually opens the database
    
    'Now Import data from Excel sheet to Database (in "tbl_tempImport" table)
    oAppAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbl_tempImport", xlFullFilePath, True, "Sheet1!A:Z", True

    'Refresh, Save and Close the database
    oAppAccess.CurrentDb.TableDefs.Refresh
    oAppAccess.DBEngine.Workspaces.Refresh
    oAppAccess.Quit
    Set oAppAccess = Nothing
End Sub

I hope now above sample gives some more clarity that what I am actually looking for...

Note - This line "oAppAccess.OpenCurrentDatabase dbFullFilepath" in above code opens the Access Database and make it visible, which it should not be. So also looking for ADODB method.

I highly appreciate your great efforts!
Regards

[ItsMe]

Just so you know, I do not use Excel to do the things you are trying to do. But, I might be able to help. It is just that I am not inclined to open up developer options within Excel right now and start testing stuff. It sounds like you are concerned about opening Access. I would not be too concerned. Just be aware that you can have issues when updating records at the same time others are updating records. If what you are trying to do is grab snapshots or append records to Access, the code would be easier than updating records that reside with the Access DB. If I understand correctly, you need snapshots so you can populate cells in Excel.

What you are doing here is early binding and indicates that you have Access referenced within your Excel Workbook.
Dim oAppAccess As Access.Application

You can have several workstations do the same thing, open an instance of the same file at their respective workstation. This would be like going to multiple workstations and double clicking the same Access file within a share.

Here you are instantiating your object
Set oAppAccess = CreateObject("Access.Application")

After you do this (instantiate), you might be able to access the hidden property, if it has one. Since you are doing early binding, type oAppAccess and dot to get intellisense and look for Hidden or Visible.

Now, when you open the instance, it may provide you with what your are looking for. Understand that the methods you are using here will require the Access runtime engine. So your workbook may not work at every workstation.

[SunOffice]

Thank you ItsMe!
If you or anyone could please provide some code for a better method, will be more helpful.

Regards,

[ItsMe]

Thank you ItsMe!
If you or anyone could please provide some code for a better method, will be more helpful.

Regards,

A better method than what, exactly? Are you simply trying to hide the Access application? Did you try adjusting the Visible or Hide property of your Access Object?

Did you look at the link to the support library provided in post #5? I would try to get it accomplished within Excel and via the OpenDatabase method.

[Micron]

An Excel instance opened from Access is either .visible = false or .visible = true. I doubt it is any different for showing/hiding an Access instance. So I would try
with oAppAccess

.visible = false
.docmd.setwarnings false
.etc if needed

end with
or use them individually if the with / end with block does not work.

[Actually, I am unable to identify that how to use ".TransferSpreadsheet acImport" trick using ADODB codes.]
I don't understand that statement at all and not just because it has nothing to do with ADODB. You already have the code to transfer the spreadsheet data, which is not a method of ADO. The only thing I don't recognize is why the last True parameter is there. Not saying it is wrong, just that I don't know of its purpose. If you have the right protocols (not trying to EXPORT text as numbers, dates as text, etc.) and if you have the right parameter for the sheet version (acSpreadsheetTypeExcel8), correct path, header parameter, sheet name and range, then what's the problem? If it's the visibility of the application, I think you now have what you need for that too. Unless it's generating errors, which you have not indicated is the case.

[ItsMe]

...Actually, I am unable to identify that how to use ".TransferSpreadsheet acImport" trick using ADODB codes...

That is because it is a method of the DoCmd Object.

[SunOffice]

That is because it is a method of the DoCmd Object.

=

I have found an example about ADODB trick as below:
"Command.Execute method":
http://www.java2s.com/Code/VBA-Excel...cutemethod.htm

Sub CommandAndExec()

   Dim conn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim rst As ADODB.Recordset

   Set conn = New ADODB.Connection
   With conn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & CurrentProject.Path & "\mydb.mdb"
           .Open
   End With

   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandText = "Select * from Customers"
   End With

   Set rst = cmd.Execute
   MsgBox rst.Fields(1).Value

   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Now We can see in above codes that if we can connect database through ADODB and perform SQL query, then why we cannot import data from Excel sheet to a Table in Access database.
...so does it mean that we cannot import data from Excel sheet to Access Table using ADODB methods in VBA codes?

I highly appreciate your great efforts!
Regards

[ItsMe]

I do not know why ADODB was ever mentioned. It is likely your best option is to use the OpenDatabase method of your Workbook.

If you need professional assistance getting your project back on track, I may be able to help you. If you are interested in some consulting you can send me a PM and we can discuss it further. Otherwise, I recommend addressing my questions within this thread that have gone unanswered.

[Micron]

I agree, ItsMe. We are getting nowhere with this "ADODB transferspreadsheet trick". This one's all yours!