bytes access nubie
Last Activity: Aug 1 '16, 08:11 PM
Joined: Oct 24 '08
Location:
-
bytes access nubie started a topic How do i access tables exported from access into SQL Server Expressin SQL ServerHow do i access tables exported from access into SQL Server Express
I am an absolute novice when it comes to the world of SQL and SQL Server Express. I downloaded from Microsoft & ran executeables for SQLEXPRWT_x86_E NU, SQLEXPR32_x86_E NU & SQLManagementSt udio_x86_ENU. I open SQL server 2014 import and export data. I select the source type, Microsoft Access Database Engine & select my access database. For Destination I select SQL Server Native Client 11.0 and enter a new database name, next. I choose... -
oh yes, my apologies about the 2 posts. that is correct, thank you. however, renaming the post was not correct. thanks again. -
2 things: Whoever renamed this title is INCORRECT. I was linking to MULTIPLE tables, not two. Someone incorrectly changed TO to TWO. Also, not sure why the answer is not displayed in the post by jforbes as follows:
The picture is getting clearer. I would guess the File is being found since you are getting past line 13 which is testing for it's existence. I should have realized that if that was working, the file extension is included...Leave a comment:
-
No, thank you. I actually marked jforbes's response as the final answer (although i'm not seeing it in this thread). his code did the trick for me. Thank you!Leave a comment:
-
just to close this loop, the databases have different names, but each would only have 1 table. I was experimenting w/access but this was to address an issue where we have a great number of DBF tables that contain historical data which 2013 doesn't recognize. We wanted to convert the tables to individual access databases. some of these tables have a tremendous amount of info, they are data sources. We have the 2nd piece of the equation that incorporates...Leave a comment:
-
wooohooo!! thank you!!! worked!!Leave a comment:
-
actually, access tells me the first table name, it says it can't find the folder. so it's having problems finding the actual locationLeave a comment:
-
thanks again. the .accdb is included in the table (see below):
FolderName
AUTH.accdb
MONTHS.accdb
PHASE.accdb
ACCT_LST.accdbLeave a comment:
-
code to link two tables in multiple databases in a folder
I have a macro that's able to pull all the db names from a folder into a table. the 2nd piece is to link to all of the tables in those databases.
The databases each have 1 table. the table & db name are the same (eg db name COST.accdb, table name COST).
any help is appreciated. -
unfortunately that did not do it. I get a message that access database engine could not find the object... and lists the first db name in the table. here's the current code:
...Code:Function LinkTable() Dim rst As DAO.Recordset Dim strPath As String Dim strFile As String strPath = "D:\Temp\Testing\" Set rst = CurrentDb.OpenRecordset("tblDirectory", dbOpenTable) DoLeave a comment:
-
after clicking debug access stops on the line DoCmd.TransferD atabase acLink, "Microsoft Access", Dir("D:\Temp\Te sting\" & rst!FolderName) etcLeave a comment:
-
access says, "error 3024, cannot find the file..." and displays the path of my default folder (the documents folder) and the name of the first db in the list. thank you for looking.Leave a comment:
-
DoCmd.TransferDatabase - use a variable for source
Attached is code to link tables in databases that are in a folder. My variable for the source in the DoCmd.transferD atabase code Dir(strPath & rst!FolderName) is not working right. I want to loop through the db names to link the tables.
each db has only 1 table which is named the same as the db. We have a macro that pulls all the db names from the folder into a table called tblDirectory into the field FolderName (that's a bit misleading,... -
Thank you so much ADezii i'll take a stab at it. And thanks very much for the word of encouragement. I guess at this point i feel more comfortable with Access than VBA. If you have any VBA intro books, websites or online courses you'd recommend I'd appreciate it. Thanks again!Leave a comment:
-
Thank you very much ADezzi, the eaders came into the excel file! Thanks so much for all of your help. Now unfortunately I'm seeing that the criteria for the queries ((qdf.Name, 6) = "qryLoc" )will not work because there are several other query names that start w/the same first 6 characters that do not filter by location. These queries are used early on in the process & build upon each other (this db was not created by me). i was hoping...Leave a comment:
-
yes, thank you, it would be helpful to have the field names from the query (eg, week ending date, office location, emp name, emp ID, etc...) pulled in as the first row in the excel file as the VB code pulls data based on those field names. What i was trying to say was that if it was easier programatically to include that header row from every query, that would not cause a problem with our code/reports. As always, i am very thankful for your help...Leave a comment:
-
Thank you ADezii, this worked and I didn't wait very long, less than 10 mins. i just have 3 queries setup as a test which returned about 200,000 records in excel. The application window remained open (that must be the change in line 16), does not close but i can work with that.
Is there a way to pull the header row into excel? extra headers from each query is ok because the code will filter the data by location name. Thank you again...Leave a comment:
-
Oh that was a stupid error, my apologies. i fixed that, re-ran & got same error. i try to open the excel file. I get the msg it's already open & re-opening will loose my changes. I answer No & I do see all the data from the 3 queries i setup. I've attached latest code again. many, many thanks.
...Code:Sub ExportData() Dim appExcel As Excel.Application Dim rst As DAO.Recordset Dim MyDB As DAO.Database
Last edited by Rabbit; Nov 12 '13, 07:34 PM. Reason: Please use [CODE] and [/CODE] tags when posting code or formatted data. Third and final warning.Leave a comment:
-
Hi Adezii. when running the new code an error displays, Run-Time Error 9 Subscript out of Range. Clicking on debug highlights the new line of code. Thank you so much again for the help that you've provided. I'm posting the current code below:
...Code:Sub ExportData() Dim appExcel As Excel.Application Dim rst As DAO.Recordset Dim MyDB As DAO.Database Dim qdf As QueryDef Dim intNumOfLocQueries As Integer
Last edited by Rabbit; Nov 12 '13, 05:07 PM. Reason: Please use [CODE] and [/CODE] tags when posting code or formatted data. Second warning.Leave a comment:
-
thank you adezii! I hit a very busy period at work. hopefully i can try this tomorrow.Leave a comment:
No activity results to display
Leave a comment: