none
OpenCurrentDatabase in memory only RRS feed

  • Question

  • I have some handy, short code in Excel that uploads the worksheet data to the tblJudgments in the given database. 

    It does what I need, BUT it also opens the database on the screen. I want this to be more seamless. Just upload the data and not display the database. 

    Any suggestions for modifying my code, or an alternate method to get my excel data to import to an Access 2016 table?

    Sub UpdateJudgmentTable()
        Dim strFile As String
        Dim TableName As String
        Dim acc As New Access.Application
        
        acc.OpenCurrentDatabase "C:\MyPath\MyDatabase.accdb"
        strFile = ThisWorkbook.FullName
        DoCmd.SetWarnings False
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblJudgments", strFile, True ', strRange
        DoCmd.SetWarnings True
            
        MsgBox "Done"
    End Sub

    Friday, February 5, 2016 5:01 PM

Answers

  • You could always use DAO to open a database object, open a recordset on tblJudgments, and loop through your range using the recordset to add each new record.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by David_JunFeng Tuesday, February 16, 2016 9:51 AM
    Friday, February 5, 2016 9:10 PM

All replies

  • Hi. Just curious... Are you executing this code in Excel?
    Friday, February 5, 2016 5:28 PM
  • Can you add this line of code right after acc.openCur...?

           With acc
               
    .Visible = False


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 5, 2016 5:36 PM
  • Assuming that your code works, you could always set the visible property of Access to false.

     acc.Visible = False

    Friday, February 5, 2016 5:40 PM
  • WillNick, setting acc.visible to false did not prevent the database from appearing. 

    theDBGuy, yes this code is in Excel

    I'm open to some other ideas.

    tod

    Friday, February 5, 2016 5:59 PM
  • theDBGuy, yes this code is in Excel

    I'm open to some other ideas.

    tod

    Hi tod,

    I don't code in Excel but does it have an equivalent method as TransferSpreadsheet for exporting the data? If it does, you should be able to use it without needing an Access object first.

    Just my 2 cents...

    Friday, February 5, 2016 6:43 PM
  • Are you showing all your code?  I don't normally use Access.Application objects so I'm a bit unfamiliar but this code makes it look like you're running DoCmd from your Excel instance and not from the Access.Application instance which doesn't seem like it should work at all.  In other words I'd expect to see something like

    With acc
     .DoCmd.OpenCurrentDatabase

    or

    acc.DoCmd.OpenCurrentDatabase

    Bruce

    Friday, February 5, 2016 6:45 PM
  • I have the access library referenced in Excel. I know this is a bass akwards way of doing things, but i like that the transferspreadsheet method asks no questions and pretty much knows what to do, assuming you've formatted your data property, have the correct column order, etc.

    I'll take DBGuy's suggestion to look for an Excel VBA equivalent. Meanwhile the procedure works just fine. 

    tod

    Friday, February 5, 2016 7:19 PM
  • You could always use DAO to open a database object, open a recordset on tblJudgments, and loop through your range using the recordset to add each new record.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by David_JunFeng Tuesday, February 16, 2016 9:51 AM
    Friday, February 5, 2016 9:10 PM