April 13, 2016 at 9:07 am
Hi all,
I would like to know if it's possible to run an export command (command prompt or powershell) for an Access database. I would like to export a table and then import this table to MSSQL Server (2012, I think). Thanks.
-Rudy.
April 13, 2016 at 10:04 am
The short answer is no - the longer answer is you can do it by running a command prompt to start Access, do the export in code from an auto-run macro or VBA, and then close Access.
My solution would be to store the table in SQL Server to begin with, and link the Access front-end to the SQL Server table.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 13, 2016 at 10:32 am
April 13, 2016 at 10:33 am
Thanks, WendellB. Unfortunately, I'm not familiar with the entire setup, so I may not be able to do as you suggested with storing in MSSQL and create a link from MS Access to MSSQL. So I'm left with possibly creating a macro in access to create an export file. I'm testing with this now, but I'm not sure how to go about this. I see many forums suggesting to use RunSavedImportExport action in a macro, but this is not listed as an action.
April 13, 2016 at 10:46 am
I didn't read all the way through this, but it's on a reliable site. A google search provides several examples....
April 13, 2016 at 10:52 am
Actually, yet another snag 🙁
I will not be allowed to install MSSQL on the machine. I will only be allowed to export from the MS Access DB, and I'll import to another server once the backup file is downloaded to there.
April 13, 2016 at 11:06 am
For the record, you would not install SQL Server on the Access machine. You'd run the package from the same SQL Server that you are trying to move the data to.
April 13, 2016 at 11:17 am
I think they're suggesting I take the simpler approach of writing the export in VB Script, which I found something like:
Dim connStr, objConn, getNames
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\TestUser\Documents\TestDB.accdb"
Set objConn = CreateObject("ADODB.Connection")
objConn.open connStr
Set rs = objConn.Execute("SELECT * FROM MYTABLE")
DO WHILE NOT rs.EOF
getNames = getNames + rs.Fields("COLUMN_1") & ","
rs.MoveNext
Loop
objConn.Close
Set rs = Nothing
Set objConn = Nothing
MsgBox getNames
April 13, 2016 at 11:18 am
just curious....how many rows/columns in the Access table?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 13, 2016 at 12:00 pm
At the moment, I have no clue of how many columns. I will need possibly only two columns, (e.g., name, course, grade). As for rows, it may be a lot more; going by the example of student name/course/grade where one student may have a lot of courses, each with a grade.
April 14, 2016 at 11:10 am
Is this a one-time task, or do you need to do it repeatedly? If it's a one-time task, you might just export it to Excel - a built in feature in Access - and then take it to the SQL Server system and import it using the SQL Server tools mentioned above. If it's a repetitive task - I would install and ODBC connection to the SQL Server system, and then put the table in SQL Server permanently, and use the Access Linked Table Manager to connect to it from the Access workstation.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 14, 2016 at 12:02 pm
WendellB,
This was planned to be a daily export/import task. But looks like I'm off the hook for now since they're revamping and migrating their data to SQL Server 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply