Import tables function (1 Viewer)

[tucker61]

I have the code below to import tables from my Master database to my Dummy database.

The problem i have is the the Master database table is a link to a SQL table, and i want it to convert to a local table (Taking too long to pull data from SQL Server), and not just copy the link.

Is there are way using the code below to convert the link to a local table - or am i best just creating a make table query to make a local table ?

Function GetLatestData()
Dim Index As Recordset
Dim i As Integer
Dim TableSet As Integer
        Set Index = CurrentDb.OpenRecordset("SELECT * FROM tblImportTables;")
        If Not Index.EOF Then
            Index.MoveLast
            TableSet = Index.RecordCount
            Index.MoveFirst
            While Not Index.EOF
                i = i + 1
                If ObjectExists(Nz(Index("Table_Name"), "")) Then
                    DoCmd.DeleteObject acTable, Nz(Index("Table_Name"), "")
                End If
                DoEvents
                DoCmd.TransferDatabase acImport, "Microsoft Access", LiveDB, acTable, Nz(Index("Table_Name"), ""), Nz(Index("Table_Name"), ""), False
                DoEvents
                Index.MoveNext
            Wend
        End If
        Index.Close
        Set Index = Nothing

    DoEvents
End Function

 

[arnelgp]

Is there are way using the code below to convert the link to a local table - or am i best just creating a make table query to make a local table ?

you already have a working solution.
you'll need to delete the Linked table anyway (only one Name can exists on the db, whether linked or local).

 

[isladogs]

You can right click any linked table and select Convert to Local Table.
Or if you want code to do this let me know and I'll attach my Link2Local procedure which is very simple.
Either way, no need to use a make table query

 

[oleronesoftwares]

Another way to convert linked table to local table is below

1. In the Database window, select the linked table that you want to convert to a local table.
2. On the Edit menu, click Copy.
3. On the Edit menu, click Paste.
4. Type the name for the new table in the Table Name box.
5. Click Structure Only (Local Table) if you want to create an empty table based on the structure of the linked table. Click Structure and Data (Local Table) if you want the new table to contain the data and structure of the linked table.
6. Click OK.

Though i am curious as to the reason the linked table(in SQL) takes time to pull data, having your back end in MS SQL has its advantages.

 

[tucker61]

Another way to convert linked table to local table is below

1. In the Database window, select the linked table that you want to convert to a local table.
2. On the Edit menu, click Copy.
3. On the Edit menu, click Paste.
4. Type the name for the new table in the Table Name box.
5. Click Structure Only (Local Table) if you want to create an empty table based on the structure of the linked table. Click Structure and Data (Local Table) if you want the new table to contain the data and structure of the linked table.
6. Click OK.

Though i am curious as to the reason the linked table(in SQL) takes time to pull data, having your back end in MS SQL has its advantages.

I wish I knew why it takes so long.

I have some data in amazon AWS / sqlserver that I use to look up against, and my main data is kept in a back end with aws.

I have 15 reports that export every morning, some days to export all 15 reports takes approx 6 minutes, other days same reports can take over 1 hour.

So I was thinking, the 2 main tables I use to look up against, make copies of the tables in my back end, and relink my front end to them. Hoping that this would speed up the process.

 

[oleronesoftwares]

I wish I knew why it takes so long.

I have some data in amazon AWS / sqlserver that I use to look up against, and my main data is kept in a back end with aws.

I have 15 reports that export every morning, some days to export all 15 reports takes approx 6 minutes, other days same reports can take over 1 hour.

So I was thinking, the 2 main tables I use to look up against, make copies of the tables in my back end, and relink my front end to them. Hoping that this would speed up the process.

Using Unbound forms is a better approach, i don't know if that is what you used?

 

[Pat Hartman]

(Taking too long to pull data from SQL Server)

Queries against ODBC BE's should have select clauses and they should NOT use VBA or UDF functions in the Where clause. You do NOT want to do anything in the query that will force Access to bring down the entire table and keep it locally in memory. Also tables need to be optimized properly with the appropriate indexes to facilitate joins and selection criteria. One point is that Access automatically makes an index on the FK when you define a relationship between two tables. This index is hidden so you don't ever see it although when you upsize, it gets ported also. In SQL Server, you need to make these FK indexes specifically yourself.

If your query is taking too long, fix the problems. Access attempts to make every query a pass through query unless you prevent it by doing something incompatible with T-SQL.

 

[bastanu]

Please feel free to use my utility that converts any front-end into an all-in-one for reporting purposes (kind of the opposite of the built-in database splitter):

FBA Fuze - All-in-one database creator - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,