Create Access table structure from SQL db but import bcp file

Posted on 2000-04-24
Last Modified: 2012-06-27
Hello Experts,

I have a slow (dialup) link to the network where my SQL 7.0 server resides. In certain situations I have the ability to have the table data sent to me via a fast link in individual tab-delimited bcp text files.

It is sometimes useful to make a local "snapshot" of the data in an Access MDB.

I would like to create a VB utility which will do the following:
1. Create a new mdb with blank tables of the appropriate structure by reading the SQL table structure over the slow link.
2. Import the bcp text data into these tables. The bcp files are named in a consistent manner based on the SQL table names.

Any ideas on how this could be done?

Question by:slpatches
  • 6
  • 4
  • 2
  • +2

Expert Comment

ID: 2745702
Well, if what you have to do is export the structure i'd suggest you to use ADOX (you will have to investigate it by yourself because I don't have any code writen about it), but it would be easier if you have an empty copy of the .mdb in the machine and just copy it.. about importing the data i don't have very much experience working with access on this matter .. SQL Server 7.0 has an object called SQLDMO with which you can alter the database, do bulk copies, etc. very easily, perhaps Access have something like that, but i'm not sure..

Author Comment

ID: 2745929
I was looking for at least some limited sample code. For example, how to instantiate the appropriate ADOX object and set the key properties. I can increase the points if someone says they have the code but 100 pts isn't enough.

Keeping a blank copy of the MDB isn't an option, because there are about 180 tables and they change structure slightly over time and new ones get added.
LVL 14

Expert Comment

ID: 2746269
You can create Access tables via code using DAO (or ADO, but I'm not there yet).  Here's some DAO code that creates a table and adds some fields based on a Word document.  You could modify this code to read the field names from other sources, such as your linked SQLServer table or your tab-delimited text file:

Function ImportWordDoc()
  Dim x As Variant, y As Variant, i As Variant, j As Variant
  Dim tabtext As String
  On Error GoTo Errlabel

  Dim db As Database, t As TableDef, wordobject As Object, f As _
  Dim r As Recordset, filename As String

  ' Get Word Merge Document to Import into Microsoft Access.
  filename = InputBox("Enter the Word Document With its Path, to Import", "Enter FileName", "C:\MY DOCUMENTS\")
  x = InputBox("Enter the Number of Columns", "Enter # of Columns")
  y = InputBox("Enter the Number of Rows", "Enter # of Rows")

  If x = "" Or y = "" Or Dir(filename) = "" Then
    MsgBox "You must supply a valid filename, and the number of table columns and rows."
    Exit Function
  End If

  Set db = CurrentDb()
  Set t = db.CreateTableDef("IMPORT WORD TABLE")    'Remove this line if target table already exists
  Set wordobject = CreateObject("Word.Basic")
  wordobject.fileopen filename

  'Create Field Names.
  For i = 0 To x - 1                                            'Remove this line if target table already exists
    Set f = t.CreateField(wordobject.Selection(), DB_TEXT)      'Remove this line if target table already exists
    t.Fields.Append f                                           'Remove this line if target table already exists
    f.AllowZeroLength = True                                    'Remove this line if target table already exists
    wordobject.nextcell                                         'Remove this line if target table already exists
  Next i                                                        'Remove this line if target table already exists

  'Append Table to Database Table Collection.
  db.TableDefs.Append t                                         'Remove this line if target table already exists

  ' Append records from Word table into Microsoft Access table,
  Set r = db.OpenRecordset("IMPORT WORD TABLE")
  For j = 2 To y + 1
    For i = 0 To x - 1
       tabtext = wordobject.Selection()
       ' Remove any carriage returns in the table cells.
       While InStr(1, tabtext, Chr$(13)) <> 0
          tabtext = Left$(tabtext, InStr(1, tabtext, Chr$(13)) - 1) & _
          Right$(tabtext, Len(tabtext) - InStr(1, tabtext, Chr$(13)))
       r.Fields(i) = tabtext
    Next i
  Next j
  Exit Function

  MsgBox Error
  Exit Function

End Function

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

LVL 14

Expert Comment

ID: 2746647
Why don't you just use disconnected recordsets with the Open / Save methods?
LVL 43

Expert Comment

ID: 2746847
Example of using ADOX to create a database, table and fields:

Add a reference to ADO for DDL and Security to the project, then add this code, change the database to reflect your real situation, you can also change the field and table names as required to build your database.

    Dim catAccess As New ADOX.Catalog
    Dim tblValExp As New ADOX.Table
    Dim cnnAccess As New ADODB.Connection
    Dim rstValExp As New ADODB.Recordset
    catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
    catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Jet OLEDB:Engine Type=4;"
    With tblValExp
        .Name = "tblValExp1"
        .Columns.Append "Stock Name", adVarWChar, 40
        .Columns.Append "Stock Symbol", adVarWChar, 6     '1
        .Columns.Append "Stock Price", adSingle       '2
        .Columns.Append "Est Date", adVarWChar, 10           '3
        .Columns.Append "Est Year", adVarWChar, 6         '4
        .Columns.Append "Price High(0)", adSingle     '5
        .Columns.Append "Price High(1)", adSingle     '6
        .Columns.Append "Price High(2)", adSingle     '7
        .Columns.Append "Price High(3)", adSingle     '8
        .Columns.Append "Price High(4)", adSingle     '9
        .Columns.Append "Price High(5)", adSingle     '10
    End With
    catAccess.Tables.Append tblValExp
    Set tblValExp = Nothing
    Set catAccess = Nothing
    cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
    rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
    rstValExp.Fields(0).Value = "Name"
    rstValExp.Fields(1).Value = "Symbol"
    rstValExp.Fields(2).Value = 1.5
    rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
    rstValExp.Fields(4).Value = "2000"
    rstValExp.Fields(5).Value = 1.5
    rstValExp.Fields(6).Value = 1.5
    rstValExp.Fields(7).Value = 1.5
    rstValExp.Fields(8).Value = 1.5
    rstValExp.Fields(9).Value = 1.5
    rstValExp.Fields(10).Value = 1.5
    Set rstValExp = Nothing
    Set cnnAccess = Nothing


Author Comment

ID: 2747272
This code makes sense to me, but how would I make the structure of the SQL server table drive this function? (step 1)
LVL 43

Expert Comment

ID: 2747659
slpatches, this example shows how to use the openschema method to get the structure of a SQL table:

    Dim cnnSQL As ADODB.Connection
    Dim rstTable As ADODB.Recordset
    Set cnnSQL = New ADODB.Connection
    Set rstTable = New ADODB.Recordset
    cnnSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<DatabaseName>;Data Source=<ServerName>"
    Set rstTable = cnnSQL.OpenSchema(adSchemaColumns)
    rstTable.Filter = "TABLE_NAME = 'MyTable'"
    With rstTable
            strText = ""
            For intCount = 0 To .Fields.Count - 1
                strText = strText & .Fields(intCount).Name & vbTab & " = " & vbTab & .Fields(intCount).Value & vbLf
            MsgBox strText
        Loop Until .EOF
    End With
    Set rstTable = Nothing
    Set cnnSQL = Nothing

Author Comment

ID: 2748601
I assume the code above creates an Access 2000 database. When I run the code it creates an MDB, but I can't open in in Access 97 and I don't have Access 2000.

I'm having trouble changing the code to create an Access 97 database and I can't find any reference on these functions in the VB ADO documentation.  Can someone tell me how I would modify this code to work with Access 97?
LVL 14

Expert Comment

ID: 2748735
With ADO.. change your Provider to "Microsoft.OLEDB.Jet.3.51" for Access97.. "Microsoft.OLEDB.Jet.4.0" for Access 2000.

If you opt to use DAO.. use version 3.51 for Access97.. DAO 3.60 for Access 2000.

Author Comment

ID: 2748910
wsh2: When I make the changes you suggest, I get a "couldn't find installable ISAM" error on the line that sets the ActiveConnection.
LVL 14

Expert Comment

ID: 2749155
Please tell me which data source you are trying to connect. Pasting some of your code would be helpful too. Here is some background information from MSDN.. <smile>.

Recordset Persistence

With recordset persistence, you can save Recordset data and metadata as a file. Later, use the persisted file to re-create the Recordset object. The persisted file may exist on a local drive, network server, or as a URL on a Web site.

In addition, the GetString method converts a Recordset object to a form in which the columns and rows are delimited with characters you specify.


The Microsoft OLE DB Persistence Provider supports storing a Recordset object in a file with the Recordset object Save method. Later, the persisted file can be restored with either the Recordset object Open, or Connection object Execute methods.

The Recordset object is converted to a form that can be stored in a file. Recordset objects can be stored in the proprietary Advanced Data TableGram (ADTG) format, or the open Extensible Markup Language (XML) format.

Pending changes are saved in the persisted file. Therefore, you can issue a query that returns a Recordset object; edits the recordset; saves it and the pending changes; later, restores the recordset; then updates the data source with the saved pending changes.


Save a Recordset:

Dim rs as New ADODB.Recordset
rs.Save "c:\yourFile.adtg", adPersistADTG

Open a persisted file with Recordset.Open:

dim rs as New ADODB.Recordset
rs.Open "c:\yourFile.adtg", "Provider=MSPersist",,,adCmdFile

Optionally, if the Recordset does not have an active connection, you can accept all the defaults and simply code:

dim rs as New ADODB.Recordset
rs.Open "c:\yourFile.adtg"

Open a persisted file with Connection.Execute:

dim conn as New ADODB.Connection
dim rs as New ADODB.Recordset
conn.Open "Provider=MSPersist"
set rs = conn.execute("c:\yourFile.adtg")

Open a persisted file with RDS.DataControl:

In this case, the Server property is not set.

Dim dc as New RDS.DataControl
dc.Connection = "Provider=MSPersist"
dc.SQL = "c:\yourFile.adtg"


Author Comment

ID: 2751365
Here's the code that's not working. Gives the "can't find installable ISAM" error on the last line.

Dim sMDBPath As String
sMDBPath = "D:\Dev\SQL2MDB\Test.mdb"
Dim catAccess As New ADOX.Catalog
Dim tblValExp As New ADOX.Table
Dim cnnAccess As New ADODB.Connection
Dim rstValExp As New ADODB.Recordset
    catAccess.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sMDBPath
catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sMDBPath & ";Jet OLEDB:Engine Type=4;"
LVL 14

Accepted Solution

wsh2 earned 100 total points
ID: 2752023
Delete the catAccess.ActiveConnection statement.. as you have already established a connection with catAccess.Create. Additionally, what is with the

& ";Jet OLEDB:Engine Type=4;"

in your
 catAccess.ActiveConnection="Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sMDBPath & ";Jet OLEDB:Engine Type=4;"


<--- Code Begin --->

Dim strConnect As String
strConnect = "Provider=Microsoft.Jet.OLEDB.3.51;" _
      & "Data Source=D:\Dev\SQL2MDB\Test.mdb"

' Build New Database
Dim cat As New ADOX.Catalog
cat.Create strConnect

' Create Table
Dim strTable As String
strTable = "x" _
   & Format(Now, "yyyymmdd") _
   & Format(Now, "hhmmss")

Dim tbl As New Table
tbl.Name = strTable
tbl.Columns.Append "Field1", adDouble
tbl.Columns.Append "Field2", adDouble
tbl.Columns.Append "Field3", adDouble
cat.Tables.Append tbl

' Close Database
Set Cat = Nothing

<--- Code End --->


Author Comment

ID: 2752066
Thanks! That works perfectly now.

Now I just need to figure out a good way to translate all the SQL field types into Access field types. I might ask that as a separate EE question.

Thanks all. I wish I could divide the points between several people.

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

773 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