I would like to export data from an Access DB, can this be achieved through a command? I plan to automate this export.
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.