Member Avatar for RudyM

Hi all,

I would like to export data from an Access DB, can this be achieved through a command? I plan to automate this export.

  • 1 Contributor
  • 1 Reply
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by RudyM
Member Avatar for RudyM

Simplest way I found was using a VB script:

Dim connStr, objConn, getNames, FSO

connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Me\Documents\Database1.accdb"

Set objConn = CreateObject("ADODB.Connection")
Set FSO = CreateObject("Scripting.FileSystemObject")

LogFile = FSO.CreateTextFile("C:\Temp\Base.txt")

objConn.open connStr

Set rs = objConn.Execute("SELECT * FROM BASE")

DO WHILE NOT rs.EOF
  LogFile.WriteLine(rs.Fields("id") & "," & rs.Fields("fname") & "," & rs.Fields("lname") & "," & rs.Fields("street"))
  rs.MoveNext
Loop

objConn.Close

Set rs = Nothing
Set objConn = Nothing

And then run a BCP import to SQL server on the output file generated.

Edited by RudyM because: Added detail for clarification.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.