October 1, 2014 installment of the Monthly Webinar Series. A best-practices webinar explaining how best to import and export using Denali Accounting Software.
Questions & Answers from the Webinar
1. What can you do with SQL studio to import & export data? ActiveX Data Objects (ADO) provide access to any type of data source. It is a flat object model with few objects. The main objects in the ADO object model are:
Connection – Refers to the connection to the data source.
Recordset – Refers to the data extracted.
Command – Refers to a stored procedure or SQL statements that need to be executed.
Although there are many ways to return a Recordset by using ADO, this article concentrates on the Connection and the Recordset objects.
2. Referencing the ADO object library
1. Start Excel. Open a new workbook and then save it as SQLExtract.xls.
2. Start the Visual Basic Editor and select your VBA project.
3. On the Tools menu, click References.
4. Click to select the most recent version of the Microsoft ActiveX Data Objects Library check box.
Extracting the data
Type or paste the following code to extract your records:
‘ Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
‘ Assign the Connection object.
.ActiveConnection = cnPubs
‘ Extract the required records.
.Open “SELECT * FROM Authors”
‘ Copy the records into cell A1 on Sheet1.
‘ Tidy up
Set rsPubs = Nothing
Set cnPubs = Nothing
3. Verifying that the code works
1. Run the code.
2. Switch to Excel and look at Sheet1 in the workbook to view the data.
You may find more information on the MS Office web site. Make sure here any Import or Export using the SQL, you have done a “back up”. ALWAYS!
5. When importing an externally created file into the sales module is Inventory and all related tables updated with the OE data? In most cases: no unless it is coming from GL which would have related tables for your “codes” and where GL numbers go. Changing a Sales module would like changing History of old pricing, etc. and that would not change at all.
4. What about universal bridge? Yes, this can be used. The important thing to know here is the “File Structures” are much different than Pro was, and they will have different lengths. Please use help to read about the Universal Bridge and what changes are new to Denali.
5. WHEN I EXPORT AM I SIMPLY COPING THE FILES OR REMOVING THE FILES??? Excellent question. You are getting a copy of your tables to be sent to Excel, to use, to make reports, to update if and only if it is one of the reports that are on the Cougar Mountain Software list of imports.