I have an Access database with a form. On this form, I have an IMPORT button which, when clicked, needs to reach out to another database that has been converted to an Access Database Project (adp), and is sitting on a Microsoft SQL Server. This is the current code I have so far:
CurrentDb.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\CentralDatabse' ", dbFailOnError
What I was hoping for the above code to accomplish is the following:
Reach out to the adp database (named 'CentralDatabase') that is sitting on a Microsoft SQL Server.
I know that the server name is called 'A7654-xyz123-s'
In this CentralDatabase, is a table called 'People'
I want to import the entire 'People' table, and name it "tblABC" when it's imported into my database.
When I try clicking my 'Import' button, I get a runtime error 3055 'Not a valid file name'.
I know I'm missing something in my code. I tried putting the extension .adp after 'Central Databse', and this doesn't work either.
I've never done this before, so this is all new to me. Would anybody be able to tell me if I'm missing anything in my syntax?
Full Version: Tapping Into an SQL Server
First , I have absolutely no experience using an .ADP project so bear that in mind.
I would think the SQL would need to look more like this (note you left out a letter in "CentralDatabase" and you didn't specify a file extension):
CurrentDb.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\yourShareName\CentralDatabase.adp'; ", dbFailOnError
In addition, you specified the filename in UNC format. UNC format is composed of a server name ("\\A7654-xyz123-s"), a share (?) and then any additional folder/directories and an optional file name.
You have no "share" name and I'm pretty sure you need one. Depending on your system you may be able to open a Command Prompt and type "NET USE" to see drive-letter to UNC mapping. Perhaps that would help you identify the share where the "CentralDatabase.adp" file exists.
If I remember correctly the "tblABC" table must not exist or this SQL will fail so you'll need to test if it exists first and, if so, drop that table.
The "SELECT... INTO..." SQL command is unusual in that it is a Data Definition Language (DDL) type of SQL statement (it creates tables) rather than being a simple query (read-only) as we'd expect a "SELECT" SQL query to be.
The lack of a share, file extension and the missing character would explain the 3055 valid file name error.
Almost certainly there are other ways to accomplish your goal but couldn't say without more info. Perhaps you can talk directly to SQL Server and bypass the .ADP file but perhaps there is a security reason to work through the .ADP file.
I would think the SQL would need to look more like this (note you left out a letter in "CentralDatabase" and you didn't specify a file extension):
CurrentDb.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\yourShareName\CentralDatabase.adp'; ", dbFailOnError
In addition, you specified the filename in UNC format. UNC format is composed of a server name ("\\A7654-xyz123-s"), a share (?) and then any additional folder/directories and an optional file name.
You have no "share" name and I'm pretty sure you need one. Depending on your system you may be able to open a Command Prompt and type "NET USE" to see drive-letter to UNC mapping. Perhaps that would help you identify the share where the "CentralDatabase.adp" file exists.
If I remember correctly the "tblABC" table must not exist or this SQL will fail so you'll need to test if it exists first and, if so, drop that table.
The "SELECT... INTO..." SQL command is unusual in that it is a Data Definition Language (DDL) type of SQL statement (it creates tables) rather than being a simple query (read-only) as we'd expect a "SELECT" SQL query to be.
The lack of a share, file extension and the missing character would explain the 3055 valid file name error.
Almost certainly there are other ways to accomplish your goal but couldn't say without more info. Perhaps you can talk directly to SQL Server and bypass the .ADP file but perhaps there is a security reason to work through the .ADP file.
I had a couple more thoughts as I reviewed my post. I'm not sure you can use DAO to interact with an .ADP file - you may need to use ADO which involves making a reference (Alt-F11, Tools, References) to Microsoft ActiveX Data Objects 2.8 (or other appropriate version) and changing the code line to:
CurrentProject.Connection.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\yourShareName\CentralDatabase.adp'; "
But that is just a wag (wild - a** - guess) on my part. If I had more time I'd test it out for you but I'm neck-deep in a couple of projects (I'm just taking a breather...)
CurrentProject.Connection.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\yourShareName\CentralDatabase.adp'; "
But that is just a wag (wild - a** - guess) on my part. If I had more time I'd test it out for you but I'm neck-deep in a couple of projects (I'm just taking a breather...)
John,
As usual, your comments are very helpful. I am used to shared drive names, but I can't seem to find one on this case. I'll keep looking. As you know, this area is a stretch for me. Today is actually my first dive into this SQL Server "stuff." Slowly, but surely, I'll pick this stuff up.
Thanks for your input.
Rich
As usual, your comments are very helpful. I am used to shared drive names, but I can't seem to find one on this case. I'll keep looking. As you know, this area is a stretch for me. Today is actually my first dive into this SQL Server "stuff." Slowly, but surely, I'll pick this stuff up.
Thanks for your input.
Rich
I'm new to SQL Server too. Used it for the first time this month but I've been working with Oracle for 7 years and DB/2 prior to that.
From the client PC (where your .mdb file resides) you should be able to "see" the CentralDatabase.adp" file from a Command Prompt by typing the UNC path version:
dir \\A7654-xyz123-s\yourShareName\CentralDatabase.adp
if there are any spaces in your share name then you'll need to use quotes. For example if your share name was "My Share" then you'd do this:
dir "\\A7654-xyz123-s\My Share\CentralDatabase.adp"
I apologize if I'm explaining something you already know but the UNC format here is comprised of a server name, the name of a Share on that server and then the name of a file that exists on that Share (folder).
So the server name in the above example is "A7654-xyz123-s", the Share name would be "My Share" and the file name is "CentralDatabase.adp".
Drive letters are often used as an "alias" or nickname for a UNC server and share so an Administrator could set up a user to have a (for example) N: drive that points (or "maps") to "\\A7654-xyz123-s\My Share" so that you could use a shorter path/filename. In this example "N:\CentralDatabase.adp" is equivalent to "\\A7654-xyz123-s\My Share\CentralDatabase.adp".
You can see these mappings by typing "NET USE" in a command prompt window.
If the Dir command doesn't list the file then there is no way that the SQL will work since both the Dir command and the SQL are referencing the file.
From the client PC (where your .mdb file resides) you should be able to "see" the CentralDatabase.adp" file from a Command Prompt by typing the UNC path version:
dir \\A7654-xyz123-s\yourShareName\CentralDatabase.adp
if there are any spaces in your share name then you'll need to use quotes. For example if your share name was "My Share" then you'd do this:
dir "\\A7654-xyz123-s\My Share\CentralDatabase.adp"
I apologize if I'm explaining something you already know but the UNC format here is comprised of a server name, the name of a Share on that server and then the name of a file that exists on that Share (folder).
So the server name in the above example is "A7654-xyz123-s", the Share name would be "My Share" and the file name is "CentralDatabase.adp".
Drive letters are often used as an "alias" or nickname for a UNC server and share so an Administrator could set up a user to have a (for example) N: drive that points (or "maps") to "\\A7654-xyz123-s\My Share" so that you could use a shorter path/filename. In this example "N:\CentralDatabase.adp" is equivalent to "\\A7654-xyz123-s\My Share\CentralDatabase.adp".
You can see these mappings by typing "NET USE" in a command prompt window.
If the Dir command doesn't list the file then there is no way that the SQL will work since both the Dir command and the SQL are referencing the file.
Hi John,
When you say type "Net USE" in the command prompt, I take it you're talking about the Dos-prompt? If so, how will it know the specific server? We have literally thousands of shared groups and possibly hundreds of servers.
Thanks,
Rich
When you say type "Net USE" in the command prompt, I take it you're talking about the Dos-prompt? If so, how will it know the specific server? We have literally thousands of shared groups and possibly hundreds of servers.
Thanks,
Rich
John,
I "think" I figured it out. Unfortunately, the path isn't fully displayed.
Don't sweat it. I'll get into the office tomorrow and find out. This is the least of my problems, believe it or not.
Have a great evening!
I'm back in California after living in Chicago for 1 1/2 years.
Rich
I "think" I figured it out. Unfortunately, the path isn't fully displayed.
Don't sweat it. I'll get into the office tomorrow and find out. This is the least of my problems, believe it or not.
Have a great evening!
I'm back in California after living in Chicago for 1 1/2 years.
Rich
Lucky you. We have 6 inches of white stuff coming this weekend. L.A. sounds pretty good right now....
Yes, when I say "Command Prompt" I'm referring to what us old-timers
called the "DOS Prompt".
The "Net Use" command only lists mapped drives known (defined) on the PC you are on. If you have mapped drives then someone assigned them to you when they created your logon ID or, perhaps, you defined them for yourself.
The GUI way to see this is to open "My Network Neighborhood" or "My Computer" and see what's available. Network Neighborhood usually shows all the servers you might want to "get" to.
Have a nice night.
Yes, when I say "Command Prompt" I'm referring to what us old-timers
called the "DOS Prompt". The "Net Use" command only lists mapped drives known (defined) on the PC you are on. If you have mapped drives then someone assigned them to you when they created your logon ID or, perhaps, you defined them for yourself.
The GUI way to see this is to open "My Network Neighborhood" or "My Computer" and see what's available. Network Neighborhood usually shows all the servers you might want to "get" to.
Have a nice night.
Hi just popping in to say "Yep"
:-)
In an ADP CurrentDb doesn't return an object.
Simply because it returns a DAO object pointing to the current (running instance) of the Jet database.
The Jet engine isn't present at all in an ADP - so it's empty.
Even if you add a DAO reference to an ADP (which you can do of course) you have to create the database object explicitly.
As John correctly says - you use
CurrentProject.Connection
to perform similar methods.
However if it's your intent to move data from an MDB to an ADP you should really be looking to the server database that the ADP is pointing at.
Or the other way around then you could perhaps set up a linked server.
:-)
In an ADP CurrentDb doesn't return an object.
Simply because it returns a DAO object pointing to the current (running instance) of the Jet database.
The Jet engine isn't present at all in an ADP - so it's empty.
Even if you add a DAO reference to an ADP (which you can do of course) you have to create the database object explicitly.
As John correctly says - you use
CurrentProject.Connection
to perform similar methods.
However if it's your intent to move data from an MDB to an ADP you should really be looking to the server database that the ADP is pointing at.
Or the other way around then you could perhaps set up a linked server.
Thanks LPurvis. The struggle for me is to find the path name to the server.
Hi
Path name to the server??
Path name of what? :-)
Path name to the server??
Path name of what? :-)
Excuse me if I don't know that much about SQL Servers. Yesterday was actually my first day at working on this. I am able to get into the ADP file on the server with all the tables and queries. However, I can't figure out the shared folder name. Per my discussion with John (above), I am trying to "call" the ADP file from a regular Access database that is sitting on a network PC. I've tried something similar to:
CurrentProject.Connection.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\yourShareName\CentralDatabase.adp'; " but without the "YourShareName", only because I can't find that share name, as stupid as this sounds. I even went into the ADP version and clicked on 'Connections', but still can't find it.
CurrentProject.Connection.Execute "SELECT * INTO tblABC FROM [People] IN '\\A7654-xyz123-s\yourShareName\CentralDatabase.adp'; " but without the "YourShareName", only because I can't find that share name, as stupid as this sounds. I even went into the ADP version and clicked on 'Connections', but still can't find it.
In your ADP go to a code window - hit ctrl G to open the immediate window.
Type (paste ;-) each line and hit enter to get the result and post them back here
?CurrentProject.Connection.ConnectionString
?CurrentProject.FullPath
Type (paste ;-) each line and hit enter to get the result and post them back here
?CurrentProject.Connection.ConnectionString
?CurrentProject.FullPath
The first line says:
Provider=Microsoft.Access.LEDGB.10.0;Persist Security Info=False;Data Source=A7654-xyz123-s;User ID=abcdefghij;Initial Catalog=CentralDatabase Provider=SQLOLEDB.1
The system doesn't recognize the 'FullPath' syntax, but does have just 'Path', so this is what I used:
\\A7654-xyz123-S\WorkAAABBB
It would lead one to believe that 'WorkAAABBB" is the shared folder, but this is because there are numerous shared folders that house the "shell" for the ADP (contains the forms and reports). I know that there are other shared folders called "WorkCCCDDD", "Work EEEFFF", etc that house these too.
Note: For security and privacy purposes, I changed some of the names, but not the server name.
Provider=Microsoft.Access.LEDGB.10.0;Persist Security Info=False;Data Source=A7654-xyz123-s;User ID=abcdefghij;Initial Catalog=CentralDatabase Provider=SQLOLEDB.1
The system doesn't recognize the 'FullPath' syntax, but does have just 'Path', so this is what I used:
\\A7654-xyz123-S\WorkAAABBB
It would lead one to believe that 'WorkAAABBB" is the shared folder, but this is because there are numerous shared folders that house the "shell" for the ADP (contains the forms and reports). I know that there are other shared folders called "WorkCCCDDD", "Work EEEFFF", etc that house these too.
Note: For security and privacy purposes, I changed some of the names, but not the server name.
Sorry
?CurrentProject.FullName
Yes - WorkAAABBB looks like your folder name. This is where the ADP is alright.
I don't see what the relevance is of your other folders though. Are there other ADP files?
All I'm saying is that ADPs themselves contain no data at all - it's the server they point to that we need to be pointing to from the MDB.
>>"Note: For security and privacy purposes, I changed some of the names, but not the server name. "
That's fine of course.
You changed the database name I take it?
(As there's a semi colon missing between "Central" and "Database Provider" ;-)
Was your Provider really
"Microsoft.Access.LEDGB.10.0"
and not
"Microsoft.Access.OLEDB.10.0" ??
?CurrentProject.FullName
Yes - WorkAAABBB looks like your folder name. This is where the ADP is alright.
I don't see what the relevance is of your other folders though. Are there other ADP files?
All I'm saying is that ADPs themselves contain no data at all - it's the server they point to that we need to be pointing to from the MDB.
>>"Note: For security and privacy purposes, I changed some of the names, but not the server name. "
That's fine of course.
You changed the database name I take it?
(As there's a semi colon missing between "Central" and "Database Provider" ;-)
Was your Provider really
"Microsoft.Access.LEDGB.10.0"
and not
"Microsoft.Access.OLEDB.10.0" ??
Ok...
?CurrentProject.FullName give me the following:
\\A7654-xyz123-S\WorkAAABBB\Main StorageDatabase.adp (note that there is a space between 'Main' and 'StorageDatabase')
This was the code I posted in my previous response:
Provider=Microsoft.Access.OLEDGB.10.0;Persist Security Info=False;Data Source=A7654-xyz123-s;User ID=abcdefghij;Initial Catalog=CentralDatabase;Data Provider=SQLOLEDB.1
The only things I changed above are:
A7654-xyz123-s
abcdefghij
CentralDatabase (one word)
You are correct in that the provider is really "Microsoft.Access.OLEDGB.10.0"
You are also correct about the semicolon. (yes....stupid me!)
Thanks
?CurrentProject.FullName give me the following:
\\A7654-xyz123-S\WorkAAABBB\Main StorageDatabase.adp (note that there is a space between 'Main' and 'StorageDatabase')
This was the code I posted in my previous response:
Provider=Microsoft.Access.OLEDGB.10.0;Persist Security Info=False;Data Source=A7654-xyz123-s;User ID=abcdefghij;Initial Catalog=CentralDatabase;Data Provider=SQLOLEDB.1
The only things I changed above are:
A7654-xyz123-s
abcdefghij
CentralDatabase (one word)
You are correct in that the provider is really "Microsoft.Access.OLEDGB.10.0"
You are also correct about the semicolon. (yes....stupid me!)
Thanks
You're importing server data into your Access table yeah?
(Not an uncommon requirement).
I'd probably suggest a passthrough (or an ADO recordset).
(As might have already been mentioned - you could go with a Linked server or OpenRowsource *if* you could predict the relative MDB location - but I don't see the point it's too contrived).
If you're only talking about a relatively low amount of records (perhaps a couple of hundred) then opening an ADO recordset based on the result of a Stored Proc and iteratively filling your local table from that will be pretty fast too.
IMHO when bringing data locally it should really only be for local manipulation then separate update or for reporting purposes.
In either case - it shouldn't really ever really be a huge amount of records.
Anyway.
and call it like something like
Once created you can import to your local table as
The ADO methods of opening a recordset and filling a table are pretty straight forward too - and no doubt well documented.
The advantage could be that the security info would only be persisted as long as the recordset is open.
You'd likely want to delete the Passthrough after running the append - so it's security info isn't persisted in the MDB.
We can provide an ADO example if you like.
(Not an uncommon requirement).
I'd probably suggest a passthrough (or an ADO recordset).
(As might have already been mentioned - you could go with a Linked server or OpenRowsource *if* you could predict the relative MDB location - but I don't see the point it's too contrived).
If you're only talking about a relatively low amount of records (perhaps a couple of hundred) then opening an ADO recordset based on the result of a Stored Proc and iteratively filling your local table from that will be pretty fast too.
IMHO when bringing data locally it should really only be for local manipulation then separate update or for reporting purposes.
In either case - it shouldn't really ever really be a huge amount of records.
Anyway.
CODE
Function fCreatePassThrough(strName As String, strSQL As String, strDBName As String, strServer As String, _
Optional strUserName As String, Optional strPassword As String, Optional blnReturnsRecords As Boolean = True)
Dim db As Database
Dim qdf As QueryDef
Dim strConnect As String
strConnect = "ODBC;Driver={SQL Server};Server="
strConnect = strConnect & strServer & ";Database=" & strDBName
If Len(strUserName) > 0 And Len(strPassword) > 0 Then
strConnect = strConnect & ";UID=" & strUserName & ";PWD=" & strPassword
End If
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strName, strSQL)
With qdf
.ReturnsRecords = blnReturnsRecords
.Connect = strConnect
.Close
End With
Set qdf = Nothing
Set db = Nothing
End Function
Optional strUserName As String, Optional strPassword As String, Optional blnReturnsRecords As Boolean = True)
Dim db As Database
Dim qdf As QueryDef
Dim strConnect As String
strConnect = "ODBC;Driver={SQL Server};Server="
strConnect = strConnect & strServer & ";Database=" & strDBName
If Len(strUserName) > 0 And Len(strPassword) > 0 Then
strConnect = strConnect & ";UID=" & strUserName & ";PWD=" & strPassword
End If
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strName, strSQL)
With qdf
.ReturnsRecords = blnReturnsRecords
.Connect = strConnect
.Close
End With
Set qdf = Nothing
Set db = Nothing
End Function
and call it like something like
CODE
fCreatePassThrough "qryPassThroughName", "SELECT * FROM People","CentralDatabase","A7654-xyz123-s","UserName","Password"
Once created you can import to your local table as
CODE
SELECT * INTO tblABC FROM qryPassThroughName
The ADO methods of opening a recordset and filling a table are pretty straight forward too - and no doubt well documented.
The advantage could be that the security info would only be persisted as long as the recordset is open.
You'd likely want to delete the Passthrough after running the append - so it's security info isn't persisted in the MDB.
We can provide an ADO example if you like.
Holy Smokes, LPurvis!
I didn't think it was so involved. I thought it would only be a simple SQL statement.
Give me about an hour to tinker with this, and I'll post my results back.
Thanks for your patience!
Rich
I didn't think it was so involved. I thought it would only be a simple SQL statement.
Give me about an hour to tinker with this, and I'll post my results back.
Thanks for your patience!
Rich
LPurivs,
This is amazing!
I wasn't even sure where to begin. I pasted your code into my database, without knowing if I was pasting it in the right place.
Then it worked!!!!
I'm still kind of nervous right now.... I have other questions, but I want to compose myself and calm down a bit..... Give me some time to collect my thoughts.
This is fabulous!!! (I still can't believe it worked!!!!!)
Thanks.
Rich
This is amazing!
I wasn't even sure where to begin. I pasted your code into my database, without knowing if I was pasting it in the right place.
Then it worked!!!!
I'm still kind of nervous right now.... I have other questions, but I want to compose myself and calm down a bit..... Give me some time to collect my thoughts.
This is fabulous!!! (I still can't believe it worked!!!!!)
Thanks.
Rich
LPurvis,
I have no futher questions! Everything is working fine now. In the server database (ADP), I was able to create some queries. Previously, I had to go into this ADP database, and then run each query. Once I got the results, I had to convert it to a text file, and then import it to my local Access database. This process took about 5-7 minutes.
Now, all I have to do is click a button on my local database, and it immediately creates the table. I took your code, and added some additional code that first deletes any exisiting tables and queries (tblABC and qryPassThroughName), then runs the pass through query, and then converts it to a local table (tblABC). I actually have several thousand records, but this still takes a few seconds, and is only a one-step process.
Thanks again!!!!
Rich
I have no futher questions! Everything is working fine now. In the server database (ADP), I was able to create some queries. Previously, I had to go into this ADP database, and then run each query. Once I got the results, I had to convert it to a text file, and then import it to my local Access database. This process took about 5-7 minutes.
Now, all I have to do is click a button on my local database, and it immediately creates the table. I took your code, and added some additional code that first deletes any exisiting tables and queries (tblABC and qryPassThroughName), then runs the pass through query, and then converts it to a local table (tblABC). I actually have several thousand records, but this still takes a few seconds, and is only a one-step process.
Thanks again!!!!
Rich
Well - I've been out for a few hours (at a boring day :-S) but see you've been progressing just fine with what you have.
Yep - as huge a fan I am of both Access as a development environment, SQL Server as a platform and ADO as a connecting technology - to pull large amounts of data into local tables you do sometimes have to drop back to an ODBC method (passthroughs).
Once you accept that - it's not so bad as a solution as I hope you're finding.
Equally ADO can provide the means to do so.
It's just not a one shot (i.e. SQL append) process.
I'm glad you're sorted - and you're welcome. :-)
Yep - as huge a fan I am of both Access as a development environment, SQL Server as a platform and ADO as a connecting technology - to pull large amounts of data into local tables you do sometimes have to drop back to an ODBC method (passthroughs).
Once you accept that - it's not so bad as a solution as I hope you're finding.
Equally ADO can provide the means to do so.
It's just not a one shot (i.e. SQL append) process.
I'm glad you're sorted - and you're welcome. :-)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.