Howto Retrieve Access HyperlinkBase Property From ADO?
Nov 13, 2006
I'm inserting records into an Access database using ADO. One of the fields is defined as a hyperlink. I figured out the #displayname#path#subpath# formatting, but at issue is how to describe a relative path (to a filename) - I'd like to programmatically retrieve the database property 'HyperlinkBase' so I can properly specify the relative path. Is there some provider-specific schema that I can use to get this info for the Access file the Connection object references? I find example code for getting hyperlink base and other builtin document properties for Word, PowerPoint, Visio, but can't seem to find this for Access.
I see various references to the options for a package's protection level (including http://msdn2.microsoft.com/en-us/library/ms141747.aspx) but I can't seem to find anything telling me how to actually look at and/or change the protection level of a package - thus my question is "How do I change the protection level of an SSIS package?"
Hi. I have lots of processing to do on the server - from the client(Access) I call a sproc which returns a recordset (the sproc isessentially a big "select"). With the obtained data , I need togenerate a report. How do I set the Recordsource of the report to theresult of the select sproc ?I have tried the following, but it does not work.Private Sub cmdReport_Click()On Error GoTo cmdReport_ClickErrorDim objCmd As ADODB.CommandDim intOpenObjects As IntegerDim rsTemp As ADODB.RecordsetSet objCmd = New ADODB.CommandintOpenObjects = 1objCmd.ActiveConnection = m_objConnobjCmd.CommandType = adCmdStoredProcobjCmd.CommandText = "_TestReport"Set rsTemp = objCmd.ExecuteintOpenObjects = 2Dim rpt As ReportDoCmd.OpenReport "TestReport", acViewDesignSet rpt = Reports("TestReport")Set rpt.RecordSource = rsTempDoCmd.Close acReport, "TestReport", acSaveYesSet rpt = NothingDoCmd.OpenReport "TestReport", acViewPreviewDoCmd.OpenReport "TestReport", acViewPreviewDoCmd.SelectObject acReport, "TestReport"DoCmd.MaximizecmdReport_ClickExit:If intOpenObjects = 2 ThenrsTemp.CloseSet rsTemp = NothingintOpenObjects = 1End IfIf intOpenObjects = 1 ThenSet objCmd = NothingintOpenObjects = 0End IfExit SubcmdReport_ClickError:MsgBox Err.Description, vbCritical, Me.NameResume cmdReport_ClickExitEnd SubHow can I do that, please ? Would it maybe be better to change the"Select" sproc into an Insert sproc, as in "SELECT... INTO TEMP", inorder to create a temp table on the server, then in Access link to thattable and set the recordsource onto the linked table ?Please help. Thank you very much, Alex.
I may be looking too hard for this but I can't find a way around it.
I have an Expression and in that expression, I want to access a property on the same object (it would be great to get the properties of other objects as well).
Example: I have a flat file connection where I defined the name of the flat file in my ConnectionString. I also have a variable that I have it linked to my dtsConfig which points to the proper folder name at run time.
How can I create an expression similar to this:
@[User::strFolder] + @[Connectionstring]
where @[User::strFolder] is my variable and points to the correct folder for the given server the package is running on and @[Conenctionstring] is my made up name to access the VALUE of the Connectionstring that I have for this flat file.
So if I have the following:
in my connectionstring property: flatfile.txt
in my strFolder derived from dtsConfig at runtime: E:etl_data
I would like my final connectionstring to look as follow:
E:etl_dataflatfile.txt
So far I know I can do it with two variables but it would be great if I could reuse the property values of the current object for my expressions or any other object.
Perhaps this value is available thru the script where I can access "any" property in my dtsx and store it into another variable and then use it. This option at least allows me to reuse code instead of hardcoding table name (connectionstring) into my variables.
Did I make this too difficult and there is a simple way to access an object's property inside the expression builder?
Hi guys, I'm getting this error:System.InvalidOperationException: The ConnectionString property has not been initialized when I'm trying to connect to an SQL database using the following code: Dim mySelectQuery As String = "SELECT [tabl_id] FROM [myTable]"Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("myConnString"))Dim myCommand As New SqlCommand(mySelectQuery, myConnection)myConnection.Open()Dim myReader As SqlDataReadermyReader = myCommand.ExecuteReader()While myReader.Read() Response.Write(myReader("tabl_id") & "<br/>")End WhilemyConnection.Close() and in web.config: <appSettings/> <connectionStrings> <add name="myConnString" connectionString="Data Source=100.100.100.100;Initial Catalog=myDatabase;User ID=me;Password=password" providerName="System.Data.SqlClient"/> </connectionStrings> If I place the actual connection string inside instead of trying to reference the web.config setting it works, but using 'System.Configuration.ConfigurationManager.AppSettings' returns the error. Is there something I'm doing wrong?
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Dear All,W2000Office2000Access adpSQLserver DBProblem:Adding a new property for a company in the subform.The FIRST time I Select a property in combobox CPROP_PRP_ID the subform actstrange.1. The selected value doen't show in the combobox!2. The sortorder of the subform changes!Reselecting the same value in the combobox acts normal and adding gives noproblem.Adding a second property for the same company acts normal.Please HELP, I can't solve this problem!All specifications below.Tables:COMPANY: COM_ID, COM_NAME, etc.PROPERTY: PRP_ID, PRP_NAME, ETC.COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUEFRM_COMPANY: source = table COMPANYSUBFRM_COMPRP:1. SubFrm source = view (see sql)SELECT COMPANY_PROPERTY.CPROP_COM_ID,COMPANY_PROPERTY.CPROP_PRP_ID,PROPERTY.PRP_NAME,COMPANY_PROPERTY.CPROP_VALUEFROM dbo.COMPANY_PROPERTY INNER JOINdbo.PROPERTY ONdbo.COMPANY_PROPERTY.CPROP_PRP_ID = dbo.PROPERTY.PRP_ID2. SubFrm: Order By = ViewComPrpsSubFrmSource.PRP_NAME to sort byPROPERTY.PRP_NAME3. SubFrm: Unique table = COMPANY_PROPERTY4. Controls:4.1.combobox that holds CPROP_PRP_ID (Rowsource=SELECT PRP_ID, PRP_NAME FROMPROPERTY WHERE (PRP_COMPANY = 1) ORDER BY PRP_NAME)4.2. CPROP_VALUE
Hi, does anyone know how to retrieve mistmatched records across 2 tables. To clarify, I have table A with 1175 records and Table B with 894 records. The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc but that doesn't seem to work.
SQL Server 2000 DTS Package Need to access a secure website which displays a list of available files for download. Firstly need to read that page to determine the most recent file. There is a view link to the file which uses java script to post back information for file download.
Hi guys, Working on a MS Access database, I have a table named "myTable" which contains several fields. I just want to retrieve all the fields (columns) in the myTable, without retrieving Col1 and Col2 What should my SQL string be?
I Apologize if this isn't the forum to ask this... I have a MS Access (MDB) file with a table with 2 date fields, i want to read from a dialog on my app (on MS Visual .NET Studio 2005), here's the code I've been using do far:
FechaC, FechaCob, are the two Dates I want to retrieve, but when I debug, it reads a 0 (zero) from the date fields, is there a limitation? can they be read? is there a special way to read them? > thanks in advance!
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _ System.Data.OleDb.OleDbSchemaGuid.Columns, _ New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
I am using C# in Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')"; SqlCommand exesql = new SqlCommand(sql, cn); exesql.CommandType = CommandType.Text; SqlDataReader rd1 = default(SqlDataReader); rd1 = exesql.ExecuteReader();
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
Hello!I have a question about the SqlDataSource object.If i make an SqlDataSource with the following sql statement: "SELECT COUNT(id) AS recordCount FROM tblCategory"How do i get the recordCount value to a Variable.Im writing in C#.<asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT(id) AS recordCount FROM [tblCategory]"></asp:SqlDataSource>
The objective is to configure on a dev machine. Selecting New Database User is the easy part as the ***ASPNET user can be selected from the drop down listbox. What follows and what to do next is a myriad of choices. What needs to be done next? Is there a step-by-step document somewhere that you can refer?
I'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.
Hi,Trying to get a grip on the "join" thing :)Up until now, I allways used this kinda method:"(select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0))"How can this be accomplished using joins? And if you have the time pleaseexplain the "bits" :)Thank you.--?TH
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
i have the following Problem: i need to have a database-design in which there are a variable number of customers , a variable numbers of products and a price per product per user. My solution looks like this:
Table Customer: CustomerID ...
Table Products: ProductID ...
Table Prices: CustomerID ProductID Price
Now my question: is it possible to get a Pricelist with one customer and all his prices in one row? E.g.:
CustomerID ; PriceProduct1 ; PriceProduct2 ; ....
So one Price-Column per Product, one Row per Customer? Can i do something like that with an sql-statement , view or stored-procedure so the number of Columns in the result depends on the number of Products and should be "dynamic" - which means when i add a new product to the product-table a new price-column is appended to the result?
I would like to gain data from a temporaly table created by an EXEC command. e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right. But I would like to use it: SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow WHERE...
I know that a function can return a table but an sp cannot.
